Data insertion using auto incremented column as primary key

Question

I have two tables whereby the primary key(foreign key on the other table) is auto incremented at run time (using TOAD for mysql). How can I insert data into the two tables at the same time using a transaction.

This is the ddl for the first table:

CREATE TABLE `suspendedsales` (
`SID` int(11) NOT NULL AUTO_INCREMENT,
`SequenceNo` int(11) NOT NULL DEFAULT '0',
`ProductCode` varchar(100) DEFAULT NULL,
`ItemName` varchar(100) DEFAULT NULL,
`Quantity` int(11) DEFAULT NULL,
`Discount` double DEFAULT NULL,
`ItemCost` double DEFAULT NULL,
PRIMARY KEY (`SID`,`SequenceNo`),
CONSTRAINT `SIDFKey` FOREIGN KEY (`SID`) REFERENCES `suspendedsalesdetails` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The ddl for the second table:

CREATE TABLE `suspendedsalesdetails` (
`SID` int(11) NOT NULL DEFAULT '0',
`Date` date DEFAULT NULL,
`Total` double DEFAULT NULL,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

N.B: The major challenge would be to get the auto-incremented key value from on the primary key to be inserted into the other table at run time.

Thanks in anticipation.

Answer 1

If your database is a MySql database you can insert a record in the first table use the following function

SELECT LAST_INSERT_ID();

to get the last inserted id and you can use it in the second insert. Commit all only after the second insert