FYI: Referential Integrity with InnoDB tables

[Mike Chirico]

Hopefully the following will be useful or interesting:
TIP 29: An example of using referential integrity with InnoDB tables.
Referential Integrity with InnoDB tables.

STEP 1 (First create the tables as InnoDB)
CREATE TABLE agents (
akey INTEGER NOT NULL auto_increment PRIMARY KEY,
ln varchar(30),
fn varchar(25),
phone varchar(20),
timeEnter timestamp(14))
ENGINE = InnoDB;

CREATE TABLE clients (
ckey INTEGER NOT NULL auto_increment PRIMARY KEY,
f_akey INTEGER NOT NULL,
ln varchar(30),
fn varchar(25),
phone varchar(20),
FOREIGN KEY (f_akey) REFERENCES agents(akey))
ENGINE = InnoDB;
STEP 2 (Insert entries -- successful way).

mysql> insert into agents (ln,fn,phone) values
('Anderson','Bob','215-782-2134');

mysql> select @ckey:=last_insert_id();

mysql> insert into clients (f_akey,ln,fn,phone)
values (@ckey,'Chirico','Abby','215-782-2353');

myslq> insert into clients (f_akey,ln,fn,phone)
values (@ckey,'Payne','Zoe','215-782-2352');

The "last_insert_id()" must be assigned to a variable, because the
client entries
for the two client keys have the same agent. After the first insert
into the client
table "last_insert_id()" is incremented, reflecting the new add to
the client table.
STEP 3 (Try to insert a client record without a matching agent --
unsuccessful way)
mysql> insert into agents (ln,fn,phone) values
('Splat','Sporkey','215-782-9987');

Above is ok

myslq> insert into clients (f_akey,ln,fn,phone)
values
(last_insert_id(),'Landis','Susan','215-782-5222');

Above Ok for the first record, but, below last_insert_id() has
been
incremented and the insert will be incorrect. And probably
fail,
if there is no matching "akey" in agents.

myslq> insert into clients (f_akey,ln,fn,phone)
values
(last_insert_id(),'Landis','Brian','215-782-5222');

SPECIAL NOTE (The "clients" table must be dropped before the "agents"
table)

All tips can be found at:
http://prdownloads.sourceforge.net/s...l.txt?download

Regards,

Mike Chirico