Entry

How do you enforce referential integrity between 2 tables?

Jun 19th, 2004 06:10
Mike Chirico, http://osdn.dl.sourceforge.net/sourceforge/souptonuts/README_mysql.tx

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)