3

I have some objects that will be created an entered into the database. There is no guarantee that any of their fields will be unique except for the auto-incrementing primary key. I would like to be able to access these objects later in the program using their primary key.

What is the best way to get this key out of the database? I can think of two methods to obtain the primary key after adding the object's data to the database:

  1. Find the maximum primary key as this was the one most recently added.
  2. Delete the current object, compare the data in the database with the data in the program, and then create a new object based on the row that is not contained in the program.

Both of these seem error-prone and hacky and I am wondering if there was a more standard way do to this.

  • One option is to remove the responsibility of assigning a unique ID from the database server and instead put it on the client with something like UUID or GUID. – Mike Dec 20 '13 at 17:53
7

Most databases have a way to access the last generated ID, take SQL server for example.

CREATE TABLE [SomeTable] (
    ID INT NOT NULL IDENTITY(1,1),
    FieldOne VARCHAR(MAX) NOT NULL,
    PRIMARY KEY(ID)
)

INSERT INTO [SomeTable](FieldOne) VALUES('a')

SELECT scope_identity() -- returns 1

INSERT INTO [SomeTable](FieldOne) VALUES('b')

SELECT scope_identity() -- returns 2

This has the advantage of being safe for multiple users / transactions.

The first suggestion about guessing what the next ID would be is not safe in that regard.

Your second suggestion will wreck havoc if you have any referential integrity or if there are any triggers.

  • For MySQL, you can use SELECT LAST_INSERT_ID(). Most web languages and database connectors offer a wrapper for this, so you can do something like $myDb->insert_id to access it without even running another query. – Ed Cottrell Dec 20 '13 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.