Best way to avoid duplicate entry into mysql database

Question

I have a table with 3 columns - id (pk), pageId (fk), name. I have a php script which dumps about 5000 records into the table, with about half being duplicates, with same pageId and name. Combination of pageId and name should be unique. What is the best way to prevent duplicates being saved to the table as I loop through the script in php?

Answer 1

First step would be to set a unique key on the table:

ALTER TABLE thetable ADD UNIQUE INDEX(pageid, name); 

Then you have to decide what you want to do when there's a duplicate. Should you:

1. ignore it?

   INSERT IGNORE INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo"); 

2. Overwrite the previously entered record?

   INSERT INTO thetable (pageid, name, somefield) VALUES (1, "foo", "first") ON DUPLICATE KEY UPDATE (somefield = 'first')  INSERT INTO thetable (pageid, name, somefield) VALUES (1, "foo", "second") ON DUPLICATE KEY UPDATE (somefield = 'second') 

3. Update some counter?

   INSERT INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo") ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1) 

Answer 2

You can also ignore the error with mysql: INSERT IGNORE INTO TABLE ... it will ignore the key error, skip over that insert and move on to the next.