Although this does the trick (kind of), this just turns off integrity
checking for that session, right? When I turn it back on, any statement
that would have failed but didn't is still in a failed state. In other
words, I could screw up and so something like:
SET FOREIGN_KEY_CHECKS = 0;
BEGIN;
UPDATE test_parent SET id = 6 WHERE id = 1;
UPDATE test_child SET parent_id = 782 WHERE parent_id = 1;
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
MySQL wouldn't complain in this case, I'd just have a child row who's
parent ID pointed to a non-existent parent (ID 782). Ideally, upon
reaching the COMMIT there'd be some way for me to have an error (and
have the transaction be rolled back). Here's what happens now:
BEGIN;
UPDATE test_parent SET id = 6 WHERE id = 1; -- errors
...
Here's what I'd like to happen:
-- made up syntax
BEGIN POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT;
UPDATE test_parent SET id = 6 WHERE id = 1;
-- doesn't error
UPDATE test_child SET parent_id = 782 WHERE parent_id = 1;
COMMIT;
-- now it errors (complaining about failed constraint) and rolls
-- back
Or:
-- made up syntax
BEGIN POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT;
UPDATE test_parent SET id = 6 WHERE id = 1;
-- doesn't error
UPDATE test_child SET parent_id = 6 WHERE parent_id = 1;
COMMIT;
-- no error since constraint is maintained
As you can see, I want to postpone checking until the end of the
transaction, not eliminate it altogether. While setting
FOREIGN_KEY_CHECKS to 0 gets me half-way there with a simple case (so
long as I'm careful), is there a way that is closer to what I've
described above? I'm open to the idea of having the decision be made at
table-creation time (as opposed to on a per-session basis), if that's
possible. For example:
CREATE TABLE test_child
(
id INTEGER NOT NULL PRIMARY KEY,
parent_id INTEGER NOT NULL,
INDEX (parent_id),
-- more made up syntax
FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE
CASCADE POSTPONE FOREIGN_KEY_CHECKS UNTIL COMMIT
) TYPE = INNODB;
Or something like that. Of course if auto-commit is on, then the
behavior would be unchanged.
--Matt
On Fri, 23 Jan 2004, Paul DuBois wrote:
>At 15:52 -0800 1/23/04, Matthew Bogosian wrote:
>>
>>...
>>
>>I'm trying to change the ID of one of the rows in one table and one of
>>the rows which refer to it in another table:
>>
>> BEGIN;
>> UPDATE test_parent SET id = 6;
>> UPDATE test_child SET parent_id = 6;
>> COMMIT;
>>
>>...
>>
>>Is there a way to temporarily postpone integrity checking until the
>>end of the transaction?
>>
>>...
>
>Try:
>
>SET FOREIGN_KEY_CHECKS = 0;
>
>http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
