Help with Primary keys and unique constraints

Question

In a table I've got 3 columns:

id
tag1
tag2

id is a primary key.

And i only want one unique tag1-tag2-combination in that table.

eg if one entry looks like:

id: 1
tag1: cat
tag2: dog

I dont want a second entry like this one beneath to get inserted:

id: 2
tag1: cat
tag2: dog

So i made all 3 columns primary keys but the problem is that then the second entry would get inserted since it looks in the combination of all 3 of them.

How do i solve this so that only the combination of the tag1 and tag2 is unique?

---

UPDATE: I added a unique contraint on tag1 and tag2. however, its still possible to insert:

id: 3
tag1: dog
tag2: cat

Is there a way to prevent this?

Answer 1

You should leave ID as the primary key, and then can create a unique constraint for the tag1 and tag2:

ALTER TABLE my_table ADD CONSTRAINT uc_tags UNIQUE (tag1, tag2)

With the unique constraint, you will be guaranteed that you will never have two rows with duplicate tag1 and tag2 values.

---

EDIT:

Further to your last update, you cannot enforce that with unique constraints. Keep in mind that for the database a record with (tag1 = dog, tag2 = cat) is totally different from a record with (tag1 = cat, tag2 = dog).

Probably your best bet is to redesign your database schema, as follows:

• Table "tags"
• Table "messages" (or whatever you are tagging)
• Table "tags_messages" with the following fields (message_id, tag_id)

Then you can simply set (message_id, tag_id) of the "tag_messages" table as a primary key. This will automatically enforce that there cannot be any message with a duplicate tag.

Some sample data:

Table: messages

message_id   |   title
-------------+------------------
1            |   some message
2            |   another message


Table: tags

tag_id       |   tags
-------------+-------------------
1            |   cat
2            |   dog
3            |   duck
4            |   horse


Table: messages_tags

message_id   |   tag_id
-------------+-------------------
1            |   1
1            |   2
2            |   3
2            |   4
2            |   1

Answer 2

I guess the question is, Why would you do it this way? It would help to know the business reason.

You can always SELECT DISTINCT to only get the rows with unique values.

Answer 3

You can keep the primary key on the "id" column and add a unique constraint on the "tag1" and "tag2" columns. See this link.

Answer 4

Add a unique index that combines tag1 and tag2.

http://dev.mysql.com/doc/refman/5.1/en/create-index.html

Answer 5

Depending on if and when you need to use the "unique record" in other tables, it can be argued that your "id" field is unnecessary. (ID here is a surrogate key) If you won't be using the "id" field in another table, then is really makes more sense to make your primary key the (tag1, tag2) and to remove the "id" column all together.

Answer 6

If you have some control over the order of insertion and update you can enforce uniqueness of permutation:

alter table t23 
   add constraint tags_ck check (tag1 < tag2)
/
alter table t23 
   add constraint tags_uk unique (tag1, tag2)
/

This works because the check constraint rejects ('dog','cat') as an invalid combination. Consequently the unique constraint can ensure that there is only evy one record with that particular permutation of tags.

As a solution this does require some intervention at insert and update time, which may be enough to sink this implementation for you. I know of an elegant solution whcih woks in Oracle, using a function-based index (I posted it here) but I don't think MySQL supports a similar type of index.