how to prevent of duplicating records in mysql insertion queries?

Question

i always in inserting data into a mysql table i use a select for that data before inserting to avoid duplicate records and if the query return null then i insert record.

but i think maybe it is not a professional way to do this job.

would you let me the ways you do?

Adding a suitable constraint as already suggested is usually a good way to go, but if for some reason that is not suitable, you could try a conditional insert (essentially what you are doing now, but all in one statement): http://stackoverflow.com/questions/2852935/sql-conditional-row-insert — Mike, Dec 06 '10 at 08:38
thanks Mike,but when i run such conditional query with WHERE NOT EXISTS statement mysql gives me a syntax error. — hd., Dec 06 '10 at 08:58

score 1 · Answer 1 · answered Jul 19 '13 at 14:07

You can try the following example. I would suggest you to try this first in your testing environment then you can implement this in your actual scenario.

Follow the below steps:

Step 1: create a table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `email` varchar(50) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


Step 2: Run this query multiple times and check that only one row is inserted

INSERT INTO `student` ( `name`, `age`)
SELECT `name`, `age` FROM `student`
WHERE NOT EXISTS (SELECT 1
    FROM `student`
    WHERE `name` = 'manish'
    AND `age` = '23'
    );

score 1 · Answer 2 · answered Dec 06 '10 at 08:32

if the reason you don't wish to use primary keys, or unique indexes is because of the error this will generate (which is an issue if you are inserting multiple rows on a single query), you can use the following syntax

insert ignore into [tablename] () VALUES ()

You can also use ON DUPLICATE KEY UPDATE as in to update certain fields as well.

insert into [tablename] () VALUES () ON DUPLICATE KEY UPDATE

for more information, have a look at http://dev.mysql.com/doc/refman/5.1/en/insert.html

This is a good approach if you plan on staying with MySQL for ever. I would recommend using standard SQL where possible instead. — El Yobo, Dec 06 '10 at 10:07

score 0 · Answer 3 · answered Nov 15 '12 at 19:15

0

$qry="INSERT username INTO users";
if(!mysql_query($qry))
{
  if(mysql_errno()=1062)
  {
    echo 'Unique costraint violation!';
  }
  else
  {
    //other error
  }
}

answered Nov 15 '12 at 19:15

sbrbot

6,169
6
43
74

score 0 · Answer 4 · answered Dec 06 '10 at 08:28

0

The "professional" way to do this will be using a primary key constraint.

answered Dec 06 '10 at 08:28

Kangkan

15,267
10
70
113

ok,i have a primary key and that is an id which is auto increament,but it will be changes for every record – hd. Dec 06 '10 at 08:30
my answer will allow you to set a unique index, which will get around your problem of an auto incrementing key – Jroc Dec 06 '10 at 08:34

how to prevent of duplicating records in mysql insertion queries?

4 Answers4