Duplicate Data from two column

Question

i have a table which has only two column one is pid(Photo ID) and another one is tid(Tag ID)

+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| pid | int(10) | NO | | NULL | |
| tid | int(10) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+

this table will store a photo id with a tag id, but i am facing a problem and i guess which is against normalization as well, here is my table data.

+-----+-----+
| pid | tid |
+-----+-----+
| 10 | 1 |
| 10 | 2 |
| 10 | 3 |
| 10 | 4 |
| 0 | 6 |
| 0 | 10 |
| 0 | 10 |
| 10 | 1 |
| 10 | 2 |
| 10 | 3 |
| 10 | 4 |
+-----+-----+

it store many duplicate values, how to get rid of this?
I mean, how i can store this types of row only for one time: | 10 | 1 |
I have tried with UNIQE but this will going to store a photo id or a tag id only for one time?
I'm using PHP
Thanks

Answer 1

You need a compound unique index.

Suppose your table is called photos. You can do this:

CREATE TABLE photos_new LIKE photos;
ALTER TABLE photos_new ADD UNIQUE INDEX pid_tid_index (pid,tid);
INSERT IGNORE INTO photos_new SELECT * FROM photos;
ALTER TABLE photos RENAME photos_old;
ALTER TABLE photos_new RENAME photos;

If it works out, then run

DROP TABLE photos_old;

Give it a Try !!!