Entry
Database:Relational: Key: Primary: How to get last unique primary key? [insert id / auto_increment]
May 22nd, 2005 00:47
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 20 March 2005 - 04:07 am ----------------------
Database:Relational: Key: Primary: How to get last unique primary key?
[insert id / auto_increment]
---
---
Method: Use a database specific function
---
table:
--------------------------------------------------------------------
| NR | LANGUAGE: COMPUTER | FUNCTION |
--------------------------------------------------------------------
1 IBM DB2 IDENTITY_VAL_LOCAL()
2 InterBase gen_id()
3 Microsoft Access ?
4 Microsoft SQL Server @@IDENTITY / SCOPE_IDENTITY()
5 MySql LAST_INSERT_ID()
6 Oracle LAST_VALUE
7 PostgreSQL CURRVAL()
8 Sybase @@IDENTITY
--------------------------------------------------------------------
---
---
Method: Use a middleware function
---
table:
--------------------------------------------------------------------
| NR | LANGUAGE: COMPUTER | FUNCTION |
--------------------------------------------------------------------
1 ASP.NET ?
2 C++ mysql_insert_id()
3 Delphi GetLastInsertID
4 Java getLastInsertID()
5 Perl $yourdatabase->last_insert_id
6 PHP mysql_insert_id()
7 Python yourdatabase.insert_id()
--------------------------------------------------------------------
---
This is the best approach, as it returns the value of the
auto_increment
field connected with the specific row of the query, so you can be sure
that the returned number is not affected by another connection also
doing inserts at the same time.
---
---
Method: Do an SQL query that returns the last entry, by replacing the
last 'insert' by a 'select'
In this case you replace the last
'INSERT INTO'
statement by a exactly the same query but with a
'SELECT <primary key column> FROM'
statement.
That will return of course the last insert,
and by taking the primary key column of it,
you get the last unique key.
---
This should also be an accurate method (but only if it returns a unique
value!) as you basically are querying your input back.
And this result will not be changed if e.g. other users are
simultaneously inserting or changing the same table.
---
e.g.
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE DATABASE
databaseMyTest;
-----------------------------------------------
USE
databaseMyTest;
-----------------------------------------------
DROP TABLE
tableTest1;
-----------------------------------------------
CREATE TABLE
tableTest1
(
columnPrimaryKey1 INT AUTO_INCREMENT PRIMARY KEY,
nameS VARCHAR( 50 )
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"John"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"Vanessa"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"Knud"
)
;
-----------------------------------------------
SELECT
columnPrimaryKey1
FROM
tableTest1
WHERE
nameS = "Knud"
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
This query might return multiple results, so not recommended either.
You could so add some time or date fields to make the output unique.
But possibly better use the other methods.
---
---
When you run this, this shows output similar to the following:
--- cut here: begin --------------------------------------------------
mysql> source c:\temp\ddd.sql
ERROR 1007 (HY000): Can't create database 'databasemytest'; database
exists
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.14 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.06 sec)
+-------------------+
| columnPrimaryKey1 |
+-------------------+
| 3 |
+-------------------+
1 row in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
Method: As the latest unique primary key is the highest or largest
integer, get this maximum value
---
To get the last=highest, use
SELECT
MAX( yourId )
FROM
yourTable
---
A possible problem with this approach is that there is no guarantee
that this code will return the result you are expecting.
On a busy system, there exists a possibility that an insert (e.g.
caused by other users which are accessing the database at nearly the
same time) will occur between the time necessary for the queries to
run. In that case you might get a result which is not what you expect
(e.g. the integer returns is 1 or 2 to high or low).
---
e.g.
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE DATABASE
databaseMyTest;
-----------------------------------------------
USE
databaseMyTest;
-----------------------------------------------
DROP TABLE
tableTest1;
-----------------------------------------------
CREATE TABLE
tableTest1
(
columnPrimaryKey1 INT AUTO_INCREMENT PRIMARY KEY,
nameS VARCHAR( 50 )
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"John"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"Vanessa"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"Knud"
)
;
-----------------------------------------------
SELECT
MAX( columnPrimaryKey1 )
FROM
tableTest1
;
-----------------------------------------------
---
When you run this, this shows output similar to the following:
--- cut here: begin --------------------------------------------------
mysql> source c:\temp\ddd.sql
ERROR 1007 (HY000): Can't create database 'databasemytest'; database
exists
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected (0.13 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.07 sec)
Query OK, 1 row affected (0.06 sec)
+--------------------------+
| MAX( columnPrimaryKey1 ) |
+--------------------------+
| 3 |
+--------------------------+
1 row in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
Book: see also:
---
[book: author: Greenspan, Jay / Bulger, Brad - title: MySql/PHP
Database Applications - ISBN: 0-7645-3537-4 - p. 118 'MYSQL_INSERT_ID
()']
---
---
Internet: see also:
---
[Internet: see also: http://www.google.com search for 'Database:
Relational: Key: Primary: Unique: Last: Get: How to get the last
unique primary key': http://lists.evolt.org/archive/Week-of-Mon-
20000605/102040.html]
---
[Internet: see also: http://www.google.com search for 'multiple
primary key column create': http://dev.mysql.com/doc/mysql/en/example-
auto-increment.html]
---
Database: Relational: MySql: Auto_Increment: Link: Overview: Can you
give an overview of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/36428/fid/70
---
Database: Language: SQL: Overview: Can you give an overview of links
about SQL?
http://www.faqts.com/knowledge_base/view.phtml/aid/32811/fid/54
----------------------------------------------------------------------
