CODEWITHSUNDEEP
mysql
Jonathan del Strother
Jonathan del Strother

Reputation: 2572

Manually specifying primary key values

I have a table 'posts', with a load of existing rows. For a subset of those rows, I'm creating a new table 'specialposts', with a reference to the existing row in the original 'posts' table. For legacy reasons, it would make mine & my clients' lives so much easier if for all 'specialposts' rows, their primary key was the same as the primary key in the 'posts' table.

I never create a SpecialPost without first creating its corresponding Post row - what problems might I run into by manually specifying the primary key on each specialpost INSERT, rather than relying on autoincrement?

Upvotes: 0

Views: 63

Answers (2)

spencer7593
spencer7593

Reputation: 108410

Q: What problems might I run into by manually specifying the primary key on each specialpost INSERT, rather than relying on autoincrement?

A: There is no problem (in terms of the database) in supplying a value for a column that's defined as (or as part of) a PRIMARY KEY constraint.

The problem you will have is determining/retrieving the value of the id auto_increment value of a row inserted into the post table.

For a singleton insert (inserting a single row to the post table), it's easy enough to retrieve the value assigned to the AUTO_INCREMENT column, following an INSERT:

SELECT LAST_INSERT_ID();

And you could supply the returned value in the INSERT to the specialpost table.

(Note that for multirow inserts, the value returned by LAST_INSERT_ID() will be the value assigned to the first row.)

From what you describe, it sounds like you'd also want to also define a FOREIGN KEY constraint between the two tables, with specialpost referencing a row in post.

For example:

post
( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

specialpost
( post_id INT UNSIGNED PRIMARY KEY
, CONSTRAINT FK_specialpost_post FOREIGN KEY (post_id) REFERENCES post (id)

Upvotes: 1

tadman
tadman

Reputation: 211610

An AUTO_INCREMENT column will automatically populate with a value if none has been supplied, but if you want to supply one, you're free to do so. Keep in mind that if this is a PRIMARY KEY column you will have to supply a unique ID and collisions will produce errors.

Keeping identifiers in sync between tables is fine so long as there's no risk of collision.

If you have some code that sometimes specifies fixed IDs and other times issues them automatically you might end up in trouble, you could have a race condition. To simplify things you might want to ensure that your secondary table has AUTO_INCREMENT turned off on the primary key.

Upvotes: 1

Related Questions