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]
