Capturing one auto number as foreign into another table.

[newbie14]

Dear All,
I have a table tblEventAlert as below. So what happens when the data gets insert into this table the problem is that the eventAlertID is autoincrement. Then below it I have another table. My problem is that I want the eventAlertID to be stored into the tblSCvData. I know I can have a master table and read the last value and update that table whenever I need to insert data into tblEventAlert but I am scared because my rate of insert will be high and that might cause a delay or many deadlocks too. Any other solution? Another solution is to compare eventAlertDateTime from tblEventAlert and dateTimer from tblScvData is that ok to compare based on datetime?

CREATE TABLE IF NOT EXISTS `tblEventAlert` (
`eventAlertID` int(11) NOT NULL auto_increment,
`enterpriseID` int(5) NOT NULL,
`associateID` int(5) NOT NULL,
`vehicleID` int(5) NOT NULL,
`trailerID` int(5) NOT NULL,
`masterID` int(5) NOT NULL,
`slaveID` int(5) NOT NULL default '0',
`eventAlertType` varchar(5) NOT NULL,
`eventAlertCategory` enum('D','E','S') NOT NULL,
`eventAlertDateTime` datetime NOT NULL,
`eventAlertInsertDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`eventAlertLatitude` double NOT NULL,
`eventAlertLongitude` double NOT NULL,
`geoFenceID` int(5) NOT NULL,
`eventAlertTypeSend` enum('Email','Sms') NOT NULL,
`eventAlertSentEmail` varchar(50) default NULL,
`eventAlertSentPhone` varchar(11) default NULL,
`eventAlertMessage` varchar(255) NOT NULL,
PRIMARY KEY (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=85 ;

CREATE TABLE IF NOT EXISTS `tblScvData` (
`scvDataID` int(11) NOT NULL auto_increment,
`header` varchar(3) NOT NULL,
`deviceType` varchar(3) NOT NULL,
`eventID` varchar(2) NOT NULL,
`masterSerialNumber` varchar(20) NOT NULL,
`simStatus` smallint(6) NOT NULL,
`csq` smallint(6) NOT NULL,
`mcc` smallint(6) NOT NULL,
`mccc` smallint(6) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` float NOT NULL,
`course` int(3) NOT NULL,
`dateTimer` datetime NOT NULL,
`gpsDateTime` datetime NOT NULL,
`gpsStat` tinyint(4) NOT NULL,
`unitStat` varchar(12) NOT NULL,
`serialKey` varchar(8) NOT NULL,
`digitalInOut` varchar(4) NOT NULL,
`fuelLevel` varchar(3) NOT NULL,
`battVolt` float NOT NULL,
`deviceVolt` float NOT NULL,
`odoMeter` float NOT NULL default '0',
`chkSum` varchar(8) NOT NULL,
`driverID` int(5) NOT NULL,
`groupID` varchar(8) NOT NULL,
`associateID` int(5) NOT NULL default '0',
`geoFenceID` int(5) NOT NULL default '0',
`eventAlertID` int(11) NOT NULL,
`scvDataInsertDateTime` datetime NOT NULL,
`gpsString` varchar(450) NOT NULL,
PRIMARY KEY (`scvDataID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=26224 ;

[urtrivedi]

I think you must use mysql function last_insert_id() when u insert data in tblscvdata

like

query1="insert into `tblEventAlert` ( `eventAlertID` , `enterpriseID` , `associateID` ..) values(null,'1','2')";

mysql_query(query1);

query2="insert into `tblscvdata` ( `scvDataID` , `header` , `deviceType` ..) values(last_insert_id(),'hdr','det')";

mysql_query(query2);

[newbie14]

Dear Urtrivedi,
I my case I have a java socket listener where once I open a connection I wont close. So all the socket thread share the same single connection. Will this method have a risk or better to use another method I found statement get generated key? Thank you.

[urtrivedi]

last_insert_id function of mysql takes care of all concurrency problem. So instead of building your own logic in java code, its safe to use mysql function.

[newbie14]

Dear Urtrivedi,
Now I got one more problem. In most cases base on certain criteria then only I will enter into tblGeoFenceAlert or tblEventAlert. Then I want to have two foreign both representing the primary key of these table into tblScvData? So what will be best solution in this case?

if(criteria = ...)
{
insert into tblGeoFenceAlert
}
if(criteria = ...)
{
insert into tblEventAlert
}


Insert into tblScvData().

[urtrivedi]

if(criteria = ...)
{
insert into tblGeoFenceAlert
Insert into tblScvData();//insert last_insert_id with appropirate column
}
if(criteria = ...)
{
insert into tblEventAlert
Insert into tblScvData();//insert last_insert_id with appropirate column
}

[newbie14]

Dear Urtrivedi,
Actually my codes of if and else are very complex. I have a number of inner if and else for decision making then only finally I have section on insert into tblScvData. So is there any method where on temporary basis I capture last_insert_id for both tblGeoFenceAlert and tblEventAlert then later I send them accordingly into tblScvData?

[urtrivedi]

you fetch last id in variable using sql query

"select last_insert_id()"

[newbie14]

Dear Urtrivedi,
Thank you so then after the select must I run like normal java.

String selectQuery1 = "select last_insert_id()"
ResultSet rs1 = stmt.executeQuery(selectQuery1);
if(rs1.next())
{


}

[urtrivedi]

yes