At 17:32 -0800 1/23/04, Matthew Bogosian wrote:
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
Right. You're not supposed to use it in order to do something that
can screw up your tables.
I guess I don't see the point of what you're trying to do. If you think
you may be doing something that violates a foreign key constraint, *and*
you want to roll it back if so, then just let the error occur within
the transaction and roll it back if it does.
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:
That doesn't make sense. Once you commit, you've committed. You can't
commit and then roll back.
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
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
