Kristian Köhntopp wrote: > Bennett Haselton wrote: > >>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. >> > > I was under the impression that databases enforced referential > integrity only on commit to allow precisely for such scenarios > as you describe, but I may be wrong.
It depends on the database. Anyway, there are several ways to do this. If both the referring columns *MUST* be non-NULL as well (in addition to having a foreign key), that makes it a bit tough, and you'll have to depend on the fact (or setting) that the database only enforces referential integrity at the commit. Alternatively, you can make one of the fields NULLable (i.e. don't put a NOT NULL constraint on it - say, BUS.DRIVER_ID. Heck, even DRIVER.BUS_ID can be NULLable - there's nothing to say that a driver is always behind the wheel :-) ). Then, it's perfectly safe to insert a BUS (with DRIVER_ID=null), insert a DRIVER with BUS_ID=null, and later, set either column to point to the other table. Back to the first situation: another elegant solution is to move the relationship outside both tables. E.g. you have a BUS table, and DRIVER table, and a BUS_DRIVER relationship table. If you want to associate a driver with a bus, you insert a row in BUS, one in DRIVER, and a row in BUS_DRIVER with BUS_ID and DRIVER_ID set appropriately to the rows just inserted. This does not violate any constraints, and is portable across DBs. The only drawback is that there's no way to enforce that for every BUS, there's a row in the BUS_DRIVER table. You can use JOINs to determine the relationships. -- Shankar. --------------------------------------------------------------------- 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
