none
Set Foreign Key Checks off in an insert script

    Question

  • I would like to insert data (via script) in tables without any foreign key checks. In MySQL the corresponding command is:

    SET FOREIGN_KEY_CHECKS = 0;
    [insert queries]

    Is there any equivalent in Transact-SQL respective command of SQL Server 2005 database engine?

    Best regards,
    Wolfang

     

    Wednesday, August 16, 2006 8:01 PM

All replies

  • The equivalent syntax is in the Alter Table command.

     

    Alter Table myTable
        NOCHECK Constraint All
    
    Insert ...
    
    Alter Table myTable
        CHECK Constraint ALL
    

     

    Wednesday, August 16, 2006 8:12 PM
  • 
    See if the following example helps you:
     
    ---
    use tempdb
    go
     
    create table x (id int not null primary key)
    go
     
    create table y (
     id int not null primary key,
     constraint fk_y_x foreign key (id) references x (id)
    )
    go
     
    --this fails -- FK violation
    insert y values (1)
    go
     
    alter table y nocheck constraint fk_y_x
    go
     
    --this succeeds
    insert y values (1)
    go
     
    select * from y
    go
     
    --...turn it back on...
    --note, the constraint is NOT enforced at this point for existing data!
    alter table y check constraint fk_y_x
    go
     
    --to find out if any keys violate the constraints...
    dbcc checkconstraints('y')
    go
     
    drop table y
    drop table x
    go
    ---
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    I would like to insert data (via script) in tables without any foreign key checks. In MySQL the corresponding command is:

    SET FOREIGN_KEY_CHECKS = 0;
    [insert queries]

    Is there any equivalent in Transact-SQL respective command of SQL Server 2005 database engine?

    Best regards,
    Wolfang

    Wednesday, August 16, 2006 8:17 PM
  • Dear Adam,

    thank you very much for the provided solution.  

    I am looking for a way to not alter all table with the 'alter table ... nocheck contraint' and afterwards to set back to check constraint table by table.

    In MySQL you can set the foreign key checks off for a specific script without alter each table, e.g.:

    --Create Table script
    use test;
    create table x (id int not null primary key);
    create table y (id int not null primary key,
     constraint fk_y_x foreign key (id) references x (id)
    );
    create table z (id int not null primary key,
     constraint fk_z_y foreign key (id) references x (id)
    );

    --Insert script
    set foreign_key_checks=0;
    insert y values (1);
    insert z values (2);

    Any ideas?

    Best regards,
    Wolfgang

     

    Thursday, August 17, 2006 10:43 AM
  • 
    Sorry, you can't do that in SQL Server.  The best you can do is loop over all of the tables (w/ a cursor, perhaps), alter all constraints, and then set them back at the end.  But obviously that would affect all user sessions, not just your current script.
     
    Why do you want to disable the constraints, anyway?  They're usually considered to be a good thing to have in place -- they protect your data's integrity.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    Dear Adam,

    thank you very much for the provided solution.

    I am looking for a way to not alter all table with the 'alter table .... nocheck contraint' and afterwards to set back to check constraint table by table.

    In MySQL you can set the foreign key checks off for a specific script without alter each table, e.g.:

    --Create Table script
    use test;
    create table x (id int not null primary key);
    create table y (id int not null primary key,
    constraint fk_y_x foreign key (id) references x (id)
    );
    create table z (id int not null primary key,
    constraint fk_z_y foreign key (id) references x (id)
    );

    --Insert script
    set foreign_key_checks=0;
    insert y values (1);
    insert z values (2);

    Any ideas?

    Best regards,
    Wolfgang

    Thursday, August 17, 2006 3:33 PM
  • ok, understand. I would like to use this for initial data loads in blank databases in an educational context, due to the fact that often errors were made with these inital bulk inserts.  I totaly agree that this should not be used in an production context.

    Thank you very much for your help.

    Best regards,

    Wolfgang

    Thursday, August 17, 2006 3:50 PM