 |
|
 |
| |
Developer on focus
Anton Zamov is a dipl. engineer with more than 6 years of active professional
experience and many awards ...
read more>>
|
|
 |
 |
 |
|
|
|
|
| |
|
MySQL: Updating foreign keys in a multiuser environment. Using LAST_INSERT_ID().
|
Updating foreign keys in a multiuser environment. Using LAST_INSERT_ID().
Also see (TIP 29) after reading this.
The LAST_INSERT_ID() is unique to the login session. This allows updating
of foreign keys.
CREATE TABLE keytest (
pkey int(11) NOT NULL auto_increment,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
CREATE TABLE foreignkeytest (
pkey int(11) NOT NULL auto_increment,
pkeykeytest int(11) NOT NULL,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
mysql> insert into keytest(ptext,ptype) values ('one',1);
mysql> select LAST_INSERT_ID() from keytest;
select LAST_INSERT_ID() from keytest;
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.03 sec)
mysql> insert into foreignkeytest (ptext,pkeykeytest) values ('one',LAST_INSERT_ID());
Note: If your session didn't update any records, LAST_INSERT_ID() will be zero. Never
assume LAST_INSERT_ID()+1 will be the next record. If another session inserts a record,
this value may be taken. You are assured that this value will be unique to the "session".
**SPECIAL NOTE: MySQL 4.1.2. supports UUID.
mysql> select uuid();
select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 167c1afe-0a0f-1027-891e-0004e222b485 |
+--------------------------------------+
1 row in set (0.00 sec)
"A UUID is designed as a number that is globally unique in space and time.
Two calls to UUID() are expected to generate two different values, even if
these calls are performed on two separate computers that are not
connected to each other."
So in the future, UUID() could be used as a better primary key. The advantage
being tables from a one server could be up-loaded to a second server without
worrying about duplicate keys.
|
About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GPU Free Documentation License)
Last Updated: Tue Jul 20 12:14:51 EDT 2004
|
|
|
|
|
|
|
|
