Hi! >Hi! > >>>>>> "Arjen" == Arjen G Lentz <[EMAIL PROTECTED]> writes: > ><cut> > >>> 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? > >Arjen> You can do this by putting the two updates inside a transaction, using the >Arjen> InnoDB table type instead of MyISAM. >Arjen> No problem. > >Note first that foreign keys can't be used to solve all problems of >references integrity as a foreign key can only refer to a primary key >in another table.
In InnoDB it can refer to any kay. See the manual at http://www.innodb.com about defining foreign keys. >I am quite sure that the referential integrity is checked per >statement, not per transactions. (Which is a common thing in many >databases). InnoDB checks foreign key constraints immediately. They are not delayed to the transaction commit. >If this is the case, then you can't solve this by wrapping the updates >in two transactions. The way to solve this is the following: (1) Create table T2 with no foreign key constraints defined. (2) Create table T1 which refers to T2. (3) Insert a dummy row D2 to T2. (4) Insert a dummy row D1 to T1. (5) Drop table T2. (6) Create table T2 which refers to T1. (7) Insert a dummy row D2 to T2. Now you have circular references D1 <-> D2. To insert a new pair of rows R1 <-> R2 to T1 and T2 you can make them first refer the dummy row in the other table, and then update them to refer to each other. I think better not use circular references :). >Regards, >Monty Regards, Heikki http://www.innodb.com/ibman.html >PS: Sorry, but I didn't have time to test this properly as I am > answering this on my laptop... > >------------------------------------------------------------------------------ --------------------------------------------------------------------- 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
