Autonumber assignements

kalmon · #1 (**permalink**) 05-14-08, 16:30

I am trying to find a way to get the autonumber of a table as soon as its entered into the database. How can this be done?

Here is an example and what I have tried:
I have a table that has a one to zero or one relationship with another table. At entry time if the item is of a type computer the user has to enter additional data about the item. I am pretty sure the insert has to be done in three(maybe two) stages. One: insert the asset. Two: get the just inserted asset number. Three: insert the detail. The problem is that I need to get the asset number of the item that was just inserted.

I have been using: SELECT assetID FROM asset ORDER BY assetID DESC; The first record returned will be the last one added. The problem is that what if another user adds a record at the same time? however unlikely it will eventually happen and it would return the wrong ID. How can I prevent that? Trigger maybe?

Here are the tables.
Table1:

Code:

CREATE TABLE `asset` (
  `AssetID` int(10) unsigned NOT NULL auto_increment,
  `assignedEID` int(10) unsigned default NULL,
  `pdate` date default NULL,
  `MFGID` int(10) unsigned NOT NULL,
  `PTID` smallint(5) unsigned NOT NULL,
  `ADesc` text collate latin1_general_ci,
  PRIMARY KEY  (`AssetID`),
  KEY `FK_Asset_Employee` (`assignedEID`),
  KEY `FK_Asset_prodtype` (`PTID`),
  KEY `FK_Asset_mfg` (`MFGID`),
  CONSTRAINT `FK_asset_mfg` FOREIGN KEY (`MFGID`) REFERENCES `manufacturer` (`ManufacturerID`) ON UPDATE CASCADE,
  CONSTRAINT `FK_asset_prodtype` FOREIGN KEY (`PTID`) REFERENCES `producttypes` (`ProductType`) ON UPDATE CASCADE,
  CONSTRAINT `FK_Asset_Employee` FOREIGN KEY (`assignedEID`) REFERENCES `employee` (`eid`) ON DELETE SET NULL ON UPDATE CASCADE
)
ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Table2

Code:

CREATE TABLE `computerdetail` (
  `assetID` int(10) unsigned NOT NULL,
  `CPU` varchar(20) collate latin1_general_ci NOT NULL,
  `Ram` varchar(12) collate latin1_general_ci NOT NULL,
  `HDD` varchar(12) collate latin1_general_ci NOT NULL,
  `OS` smallint(4) unsigned NOT NULL,
  `VirusScan` varchar(15) collate latin1_general_ci default NULL,
  `office` varchar(20) collate latin1_general_ci default NULL,
  `VidCard` varchar(20) collate latin1_general_ci default NULL,
  `SndCard` varchar(20) collate latin1_general_ci default NULL,
  PRIMARY KEY  USING BTREE (`assetID`),
  CONSTRAINT `FK_ComputerDetail_asset` FOREIGN KEY (`assetID`) REFERENCES `asset` (`AssetID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

healdem · #2 (**permalink**) 05-14-08, 17:42

what fornt end language are you using. .usually there is a function in that language which will retrieve the last ID

r937 · #3 (**permalink**) 05-14-08, 18:56

use the mysql LAST_INSERT_ID() function

if it's php, use mysql_insert_id()

kalmon · #4 (**permalink**) 05-16-08, 09:24

Thanks r937, I didn't know about the LAST_INSERT_ID() function.

The second part of my question I am still not sure about. What if two (or more) people insert a record at the same time? Wont that function return the same answer to all of them?

Just thinking aloud would this prevent that:
set {Variable} = INSERT INTO asset (...) VALUES (...); LAST_INSERT_ID(); I'd guess that the return would only be the second part of the statement thus would return the value as its being entered. Please Let me know.

Thanks,
-Brian

Using VB and ADO for the front end.

r937 · #5 (**permalink**) 05-16-08, 09:27

you don't need to do anything about it, mysql takes care of it

it's fully explained in the docs

Internet Services

Web Development

Digital Marketing

Consumer Tech