Unique Records

Databases
1

Hi guys,

Is there a way of creating a unique ‘group’ of fields within a MySQL table?

For example, take an address book.
There will be fields like user id (the owner of the address) and post code. Is there a way of grouping the user id and the post code together to ensure that that user doesn’t add that address again?
Also, the user must be allowed to enter multiple addresses so the user id will appear multiple times.

Any help would be appreciated.

Cheers.

2

Create a unique index composed of userid and postcode.

3

ALTER TABLE address_book ADD CONSTRAINT user_postcode UNIQUE (addr_postcode,user_id); ?

This causes errors when INSERT is used with a user id that is already defined.

4

Looking at the syntax in the manual gives me headaches :lol:
The CREATE INDEX syntax is easier.

But maybe the index name has to be moved after UNIQUE, and maybe it makes a difference adding INDEX as well?


ALTER TABLE address_book ADD CONSTRAINT UNIQUE INDEX user_postcode  (addr_postcode,user_id);
5

Thanks!! That does exactly what I want it to.
You’re right about the MySQL manual, it gives me headaches too.

Thanks again!