Home     My Faqts     Contributors     About     Help

faqts : Computers : Databases : MySQL : Tips and Tricks

Did You Find This Entry Useful? 1 of 2 people (50%) answered Yes Recently 1 of 2 people (50%) answered Yes

How do you enforce referential integrity between 2 tables?

Jun 19th, 2004 23: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)

© 1999-2004 Synop Pty Ltd