views:

73

answers:

6

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?

+5  A: 

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
Daniel Vassallo
i didnt know you could do that...perfect!
weng
Note that unlike primary keys, you can have many `UNIQUE` constraints per table.
Daniel Vassallo
what if i want this entry: id:3, tag1: dog, tag2: cat to be neglected too? how could i do this?
weng
i did..but it didnt work.
weng
@noname: I understand what you mean, now. Unfortunately you cannot enforce that with unique constraints. You must slightly redesign your database schema to make that work. You have to have a separate table for the tags and then create a 1 to many relationship by using another table.
Daniel Vassallo
so if my tag_table looks like: id, name. how would the other table looks like to solve the combination problem?
weng
@noname, check my updated answer. Let us know if you require further clarifications.
Daniel Vassallo
A: 

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.

George Stocker
It seems like a perfectly reasonable thing to do to me. Many of my tables have UNIQUE constraints on them. Finding unique values is not the same thing as ensuring them.
anon
+1  A: 

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

Jakob Christensen
+1  A: 

Add a unique index that combines tag1 and tag2.

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

Lior Cohen
is an unique index the same thing as a unique constraint?
weng
No. They can be used to achieve the same functionality, however.
Lior Cohen
+1  A: 

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.

RC
good point...but i need to use it for another table. 1+ for the comment though:)
weng
It can be argued, but only if you don't intend to ever change either of the other two fields and will never add other tables that use the PK of this table. I would always make the use of surrogate keys be the default.
anon
A: 

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.

APC