In a database that enforced referential integrity, if you say that a field
in one table is a foreign key referencing another table, then any value in
the foreign key field in the first table must reference an existing row in
the second table. (Right?)
But suppose you have a database storing, I dunno, buses, their occupants,
and the driver of each bus, so you have a table BUS, and one of the fields,
"driver", is a foreign key field specifying the SSN of the person who
drives the bus. Then you have another table, "person", with a foreign key
field "BUSNUMBER" that references the VIN number of the bus that the person
is on. Assume that every bus has people on it and every person is on a
bus, so neither foreign key field can be NULL. (Stupid example in terms of
relation to the real world, but I can't think of anything better.) In
general, you have a one-to-many relationship from one group to another, and
then a one-to-one relationship from the second group back to the first one,
and the constraints of the "real world" that you're modeling suggest that
neither one of these can be NULL.
The problem is, how would you add a new bus and a new driver to the
database? Whichever one is added first, you're going to get an error
because its counterpart doesn't exist yet, violating referential integrity.
You could always relax the NOT NULL constraint, but that might not be
desirable if you want to be consistent with the real-world situation that
your database describes. Is there a way to update two tables at the exact
same time so that referential integrity never gets violated?
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
