472,780 Members | 1,197 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,780 software developers and data experts.

FYI: Referential Integrity with InnoDB tables

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)
akey INTEGER NOT NULL auto_increment PRIMARY KEY,
ln varchar(30),
fn varchar(25),
phone varchar(20),
timeEnter timestamp(14))

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

mysql> insert into agents (ln,fn,phone) values

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

Above is ok

myslq> insert into clients (f_akey,ln,fn,phone)

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

myslq> insert into clients (f_akey,ln,fn,phone)

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

All tips can be found at:


Mike Chirico

Jul 20 '05 #1
0 1252

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Tom Gazzini | last post by:
I need to have a table that supports FULLTEXT searches. This implies that this table should be a MyISAM table. However, I also require that this table act as a parent for child tables in order...
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
by: Jimmie H. Apsey | last post by:
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential...
by: HGT | last post by:
Hello all, I am currently on a project which the source data come into the databases is always dirty (not surprisingly); however, due to the design of the database, it is very difficult to...
by: heyvinay | last post by:
I have transaction table where the rows entered into the transaction can come a result of changes that take place if four different tables. So the situation is as follows: Transaction Table...
by: Andrew R | last post by:
Hi I'm creating a series of forms, each with with around 15-20 text boxes. The text boxes will show data from tables, but are unbound to make them more flexible. I want the form to be used...
by: CPAccess | last post by:
How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table? Here's the situation: I have two tables:...
by: Wayne | last post by:
I've inadvertently placed this post in another similar newgroup, and I apologise if you get it twice. I'm building a database that consists of frontend and backend. Some of the lookup tables...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.