Matthew, sorry, InnoDB does not support deferred constraint checking. It is in the long-term TODO.
Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: "Matthew Bogosian" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, January 24, 2004 4:11 AM Subject: Re: Postponing Integrity Checking... > On Fri, 23 Jan 2004, Paul DuBois wrote: > > >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. > > But I *can*, and that's the point. I want to use the database to protect > me from myself (or my company's application from its DBAs, etc.). > > >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. > > Yes, I want all the constraints to be maintained. But the change I want > to make violates one of them, but only temporarily. > > I'm sorry for not conveying what I mean more clearly. Here's a simpler > question. Given: > > CREATE TABLE test_parent > ( > id INTEGER NOT NULL PRIMARY KEY > ) TYPE = INNODB; > > CREATE TABLE test_child > ( > id INTEGER NOT NULL PRIMARY KEY, > > parent_id INTEGER NOT NULL, > INDEX (parent_id), > FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE > CASCADE > ) TYPE = INNODB; > > INSERT INTO test_parent VALUES (1); > INSERT INTO test_child VALUES (50, 1); > > How do I execute the following UPDATE statements such that I can ensure > that all integrity constraints are maintained upon the completion of the > last one? > > UPDATE test_parent SET id = 6 WHERE id = 1; > UPDATE test_child SET parent_id = 6 WHERE parent_id = 1; > > >>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. > > Sorry, I was using the word "commit" to mean transaction barrier. What I > meant was to indicate that I am done with the transaction with a desire > to commit the changes, but only if the integrity constraints were > maintained. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
