Dringo Dringo - 1 month ago 7
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,
`DeliveryID` int(11) PRIMARY KEY AUTO_INCREMENT ,
`Tip$` float NOT NULL,
`Username` varchar(50) NOT NULL
)


And

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

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

SELECT @Var := Last_INSERT_ID();

INSERT INTO Location
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

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.

Comments