I am trying to use the insert ignore statement to put a name in a database. I do not want to create duplicates, however, the insert ignore statement I use keeps inserting!
The statement is:
INSERT IGNORE INTO firstname(name) VALUES ('Tommy')
My database shows this:
id | name
-------------
1 Tommy
2 Tommy
3 Tommy
A couple bits of information for people helping me. My id is my primary key and it is auto incremented.
My guess is that because the new id is created automatically it thinks its a new result! I am not sure how to solve this expcept with a bigger SQL query checking if the name Tommy exists first
Thanks
It's because the column name from firstname table is not unique. Try to add unique constraint using this DDL statement,
ALTER TABLE firstname ADD UNIQUE (name);
then try if it still add duplicate name. Hope it works now :)
Have you tried to set 'unique key' constraint on 'name' column?
Given your schema, using IGNORE makes no difference. IGNORE is used to ignore potential errors raised during the insertion.
To avoid duplicates in the name column, add a UNIQUE constraint on that column and drop the IGNORE keyword from the INSERT statement. If you keep using the IGNORE, the UNIQUE constraint will cause an error but it will be ignored which means in fact that the UNIQUE constraint will be ignored.
