Help with basic keys and unique restrictions

Question

In the table, I have 3 columns:

id
tag1
tag2

id is the primary key.

And I want only one unique combination of tag1-tag2 in this table.

for example, if one record looks like this:

id: 1
tag1: cat
tag2: dog

I do not want a second entry like this to be inserted:

id: 2
tag1: cat
tag2: dog

So, I made all the primary keys from 3 columns, but the problem is that then the second record will be inserted, since it looks like in a combination of all 3 of them.

How can I solve this problem, so that the only combination of tag1 and tag2 is unique?

---

UPDATE: I added a unique tag1 and tag2 tag. however, it can still be inserted:

id: 3
tag1: dog
tag2: cat

Is there any way to prevent this?

Answer 1

, tag1 tag2:

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

, tag1 tag2.

---

EDIT:

. , (tag1 = dog, tag2 = cat) (tag1 = cat, tag2 = dog).

, :

• "tags"
• "" ( , )
• "tags_messages" (message_id, tag_id)

(message_id, tag_id) "tag_messages" . , .

:

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

"id" "tag1" "tag2". . .

Answer 3

, tag1 tag2.

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

Answer 4

, " " , , "id" . (ID ) "id" , (tag1, tag2) "id" .

Answer 5

, : ? -.

SELECT DISTINCT .

Answer 6

, :

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

, ( "", "" ) . , , .

, , . , Oracle, ( ), , MySQL .