INSERTing record using auto_increment number

[bulrush]

I'm testing my application, specifically I am testing the delete function. I have one main table, which links to 5 other tables in a 1 to many relationship. So, when I delete a record from the main table, I manually cascade the deletes to records in the 5 related tables.

 

What this means is I am backing up the records using the SQL export function. The Export function creates an INSERT statement with the auto_increment number in it, which happens to be the primary key of each table. When I run this INSERT statement to restore my records to all 6 tables, will I get duplicate keys because the key is specified in the INSERT statement? Or, if there is a dupe key already existing, will Mysql automatically give that record a new key?

 

For example: say I delete in main table record with a key of "1", but record with a key of "2" still exists. I want to reinsert all my records by running the export-created INSERT statement. The INSERT has keys of "1" and "2" in the primary key field. Record 1 no longer exists, but record with a key of 2 does. What will Mysql do when it tries to insert a record with a primary key of 2?

 

Below, the "oid" is the key field which is auto_increment.

INSERT INTO `ordertip` (`oid`, `gid`, `createuser`, `createdate`, `updateuser`, `updatedate`, `ordertext`, `orderlevel`) VALUES
(1, 14, 'chuckr', '2010-05-17 05:12:11', NULL, NULL, 'To order zzz-100-a.', 0),
(2, 16, 'chuckr', '2010-05-17 05:35:40', NULL, NULL, 'to order 1 jjjn', 0);

[Mchl]

It will throw an error.

 

You should restore your backups into empty tables, so there's no way you can run into duplicate key problem.

[bulrush]

Ok. So if I delete the key value from the INSERT statement, then Mysql should assign the next key value, correct?

 

[Mchl]

Inert null instead

[bulrush]

Ok. Thanks!