What happens when you try to INSERT a duplicate record?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
steve9876
Forum Newbie
Posts: 13
Joined: Tue Jul 21, 2009 7:34 pm

What happens when you try to INSERT a duplicate record?

Post by steve9876 »

What happens when you try to INSERT a record with a UNIQUE field that already exists?

I'm creating a randomly-generated "order number" $OrderNo and of course I want to make sure it isn't already being used. Suppose I define "OrdNo" as PRIMARY KEY or UNIQUE (can I do one without the other?). What would

mysql_query("INSERT INTO Orders (OrdNo) VALUES ($OrderNo)");

do if there already is such a record?

If it would return FALSE (and presumably TRUE if the new record was created), that would do the job and also kill two birds with one stone. Or will it trigger an error condition?

(If I'm specifying only one column, can I use VALUE or does it have to be VALUES?)

I've also seen SELECT used for this purpose:

$result=mysql_query("SELECT * FROM Orders WHERE OrdNo=$OrderNo");
if ($record = mysql_fetch_array($result))...//Try Again

My question is, is the second line necessary, or can $result itself be tested to see if any such records were found?

How would I test this? The only thing I can think is to create some records and then hard-code an order number that already exists.

I also have a couple of somewhat-related questions:

As I understand it, it's perfectly all right to INSERT a record with only a few columns (or one column) specified and populate the others later, as I did in my example above. Is this right?

Does the order of the clauses in a mySql command matter? (Since they're keyword-based, I would expect not.) I've seen UPDATE syntax as "UPDATE...SELECT...WHERE...", but to me it would usually seem clearer to put the selection criteria first: "UPDATE...WHERE...SELECT...".

Thanks for your help.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: What happens when you try to INSERT a duplicate record?

Post by jackpf »

A duplicate primary key will trigger an error, so the query will return false.

You could have a function, like

Code: Select all

 
function foo()
{
if(!mysql_query("INSERT INTO table (`ID`) VALUES ('".rand()."');"))
foo();
}
 
I reckon that would work. I'm not sure but...worth a try :)
Post Reply