Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    5

    Unanswered: Deferred constraint checking

    Hi,

    I have to tables parent and child:

    Code:
    CREATE TABLE `parent` (                                 
              `id` bigint(20) NOT NULL,                            
              `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,   
              PRIMARY KEY (`id`)                                   
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    
    CREATE TABLE `child` (                                                       
                  `id` bigint(20) NOT NULL,                                                       
                  `parent_id` bigint(20) NOT NULL,                                                
                  PRIMARY KEY (`id`),                                                             
                  KEY `FK_child` (`parent_id`),                                              
                  CONSTRAINT `FK_child` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)  
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    I want to delete all rows for a special parent.id by the following SQL:

    Code:
             
    delete from parent where id = ?;
    delete from child where parent_id = ?;
    Trying this I get an error "Cannot delete or update a parent row: a foreign key constraint fails" obviously.

    Therefore I've tried:

    Code:
    START TRANSACTION;         
    delete from parent where id = ?;
    delete from child where parent_id = ?;
    COMMIT;
    which fails with the same exception. Why? At the end of the transaction all foreign key constraints are ok.

    The only working solution I've found is:

    Code:
    SET FOREIGN_KEY_CHECKS=0         
    delete from parent where id = ?;
    delete from child where parent_id = ?;
    SET FOREIGN_KEY_CHECKS=1
    BUT doing this the foreign key constraints are not checked after setting them back to 1. That means I could have done

    Code:
    SET FOREIGN_KEY_CHECKS=0         
    delete from parent where id = ?;
    SET FOREIGN_KEY_CHECKS=1
    which would not have lead to an error but should have as the constraint is violated now. Do I have to (and am I able to) force a foreign key check at this point?

    I am wondering how such frameworks like Hibernate are doing complex update statement cascades that are violating some foreign key constraints while the transaction is active but not after a commit.

    By the way: of course I could simply change the order of my statements, but that's not the case. It's just to have a very simple example.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by engram View Post
    Why? At the end of the transaction all foreign key constraints are ok.
    MySQL does not support deferred constraints.

    Additionally constraints are evaluated on a row-by-row basis, not on statement level. Which means you can't do something like this either:
    Code:
    create table fk_test (
      id          integer primary key,
      name        varchar(20),
      parent_id   integer, 
      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,'Sub1', 1);
    insert into fk_test (id,name,parent_id) values (3,'Subsub', 2);
    insert into fk_test (id,name,parent_id) values (4,'Sub2', 1);
    commit;
    
    delete from fk_test where id in (2,3,4);
    The delete statement will fail with because when it tries to delete row with the id = 2 there are still other rows referencing that one. But even though the others will be deleted within the scope of the same statement the row-by-row checking prohibits the deletion.

    I wouldn't be surprised if this might actually work sometimes due to a different (random?) order of processing the rows depending on the index availability or other optimizations.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jan 2012
    Posts
    5
    But why are frameworks like Hibernate able to do such things? I can even delete/update rows within two tables that are referencing each other. Following your explanation this must not work, but it does.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by engram View Post
    But why are frameworks like Hibernate able to do such things? I can even delete/update rows within two tables that are referencing each other. Following your explanation this must not work, but it does.
    I guess they are issuing the SQL statements in the correct order.

    As Hibernate knows the dependencies that shouldn't be too to figure it out.

    ORMs cannot really rely on deferred constraints as only a few DBMS actually support them.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jan 2012
    Posts
    5
    Ok. Presuming that the only way would be to disable the foreign key checks temporarily, is it possible to force a check after I have finished my deletes to ensure data integrity?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by engram View Post
    Ok. Presuming that the only way would be to disable the foreign key checks temporarily, is it possible to force a check after I have finished my deletes to ensure data integrity?
    The only thing I can think of is running a query that checks the presence of all children. Something like:
    Code:
    select count(*)
    from child c
    where not exists (select from parent p where p.id = c.parent_id)
    But that is hardly "forcing" it.

    I usually use Postgres if I have more sophisticated requirements like that.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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