Dringo Dringo - 1 year ago 84
MySQL Question

Inserting data into 2 tables using a variable as the ID for the foreign key

So I have two MySQL tables (Lets say these are rough tables) Location is a multivalued attribute of the Delivery, so I decided to break it off into its own table

CREATE TABLE `Delivery` (
`Date` varchar(10) NOT NULL,
`Time` varchar(10) NOT NULL,
`OrderTotal` float NOT NULL,
`Tip$` float NOT NULL,
`Username` varchar(50) NOT NULL


CREATE TABLE `Location` (
`DeliveryID` int(10) NOT NULL,
`APT` varchar(10) DEFAULT NULL,
`Street` varchar(50) NOT NULL,
`Address` int(20) NOT NULL,
) FOREIGN KEY ('DeliveryID') REFERENCES `Delivery` (`DeliveryID`);

So what I want to do is say I insert into delivery with its own respective data, and add data to the location table with its own matching id to the delivery obviously. I've been told to break multivalued attributes into their own table. So what I have been doing is

VALUES (DEFAULT, '12345','10:53am','admin','100','10');

SELECT @Var := Last_INSERT_ID();

VALUES (@Var, '1234','address',DEFAULT);

My Question is, is this bad practice? Or is there any way to get around this, like just having a bigger Delivery table that has all Location data inside of it? My fear is, that say I have multiple users of this database somehow someway they will be able to insert into the Delivery table and mess up my synchronization in the 0.0005 Seconds it takes to grab the MAX(DeliveryID) from the table? For a project of this size I know it won't be a problem, but if this were to be a large scale project can errors crop up like I have mentioned?

Answer Source

You should be using the last inserted id instead (https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id). Never encountered your method before of doing an extra select with MAX() and yes, it's possible for you to grab the wrong id with that approach.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download