Hi Bennett, ----- Original Message ----- From: "Bennett Haselton" <[EMAIL PROTECTED]>
> 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?) Yea. > 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? You can do this by putting the two updates inside a transaction, using the InnoDB table type instead of MyISAM. No problem. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- 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
