What happens when you try to INSERT a duplicate record?
Posted: Sun Jul 26, 2009 11:57 am
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.
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.