Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

    Unanswered: Should this fail?

    Hi,

    we had a discussion here whether the following DELETE should work or not. My expactation was that this should work as it is a single statement and FK constraints are evaluated on statement level (unless they are defined as deferred)

    Here is the table definition and the test data:
    Code:
    CREATE TABLE fk_test 
    (
        id integer primary key,
        name varchar(20),
        parent_id integer
    );
    
    ALTER TABLE fk_test 
        ADD CONSTRAINT fk_test_parent FOREIGN KEY (parent_id) 
        REFERENCES fk_test(id);
    
    INSERT INTO fk_test (id,name,parent_id) values (1,'root', null);
    INSERT INTO fk_test (id,name,parent_id) values (2,'two, 1);
    INSERT INTO fk_test (id,name,parent_id) values (3,'three', 2);
    INSERT INTO fk_test (id,name,parent_id) values (4,'four', 1);
    
    COMMIT;
    And this is the delete statement
    Code:
    DELETE FROM fk_test WHERE id IN (1,2,3,4);
    COMMIT;
    Now, Firebird and MySQL fail with an error message, whereas "all others" (Postgres, Oracle, DB2, SQL Server, Sybase, Derby, H2) happily delete the rows.
    We also tried to specify the ids in the IN clause in the correct order, just to test it. But that doesn't change anything (which is expected as the order in the IN clause should not have any meaning)

    I do understand that we could simply send four individual DELETE statements in the correct order. But my question is not how to make this work (with MySQL or Firebird).

    My question is more of a basic nature:
    To my understanding this is a legal statement and should work, but as there are DBMS out there that do it differently (a minority though) it could well be something that is no clearly specified.

    What are you thinking?
    Should the statement work? (from a "relational" point of view, not from a SQL or DBMS specific point of view)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    According to the ISO specs, a SQL engine ought to allow both of your code samples to run. I don't have a personal copy of the standard to be able to quote it, but the gist of the rule is that individual statements must be processed atomically (meaning that the outcome can't be determined until the statement is complete) and that the constraint checking can't be processed until after all of the intermediate operations are complete. Since your database will be in a referentially consistant state when the DELETE operation completes, that DELETE meets the ISO required test.

    As a side note, different builds of MySQL and different storage engines within MySQL will process this statment differently. Some builds/engines will handle it correctly, others will not. A few builds (mostly older innodb engines) will handle the statement differently based on external factors (other processes can influence the results of this statement).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    According to the ISO specs, a SQL engine ought to allow both of your code samples to run. I don't have a personal copy of the standard to be able to quote it, but the gist of the rule is that individual statements must be processed atomically (meaning that the outcome can't be determined until the statement is complete) and that the constraint checking can't be processed until after all of the intermediate operations are complete. Since your database will be in a referentially consistant state when the DELETE operation completes, that DELETE meets the ISO required test.
    Thanks, that supports my expectation.

    As a side note, different builds of MySQL and different storage engines within MySQL will process this statment differently. Some builds/engines will handle it correctly, others will not.
    The only engine where it can fail is InnoDB, because no other (that I know of) supports foreign key constraints.

    A few builds (mostly older innodb engines) will handle the statement differently based on external factors (other processes can influence the results of this statement).
    Would you mind going into more details here?
    It's not really important because we will need to rewrite the statements for MySQL anyways, but I'd like to know.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Many versions of the MySQL language engine will allow you to declare foreign keys. Inno-DB is the only common MySQL storage engine that enforces the DRI declarations. Declaring and enforcing DRI are two very different things in MySQL, and it is quite possible to declare DRI that won't be enforced by the storage engine.

    I'm going to move this thread to the MySQL forum so that the folks with more MySQL experience can offer their observations about specific behaviors. I've run into a number if "interesting" behaviors of Inno-DB when it is run as a user (non-root) process that have cost me a lot of hair and more experienced MySQL users may be able to offer a more informed and cogent explanation than I can.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •