You must Sign In to post a response.
  • Category: SQL Server

    How to Disable FOREIGN KEY constraint.


    Are you looking for a way to Disable FOREIGN KEY constraint ? then read this thread to know how to disable it



    I want to truncate my tables,but tables having FOREIGN KEY constraint.,so am unable to truncate the tables.i want to know how can we disable the FOREIGN KEY constraint??
    i tried the below code, but i failed.
    The code is:
    "

    alter procedure [dbo].[clearalldata]

    AS
    EXEC sp_MSForEachTable ALTER TABLE ? NOCHECK CONSTRAINT ALL
    EXEC sp_MSForEachTable ALTER TABLE ? DISABLE TRIGGER ALL
    EXEC sp_MSForEachTable DELETE FROM ?
    truncate table [dbo].[tbl_login]
    truncate table [dbo].[tbl_result]
    truncate table [dbo].[tbl_testdetails]
    truncate table [dbo].[tbl_Test]
    truncate table [dbo].[tbl_specimen]
    truncate table [dbo].[tbl_patient]
    truncate table [dbo].[tbl_specimen_type]
    EXEC sp_MSForEachTable ALTER TABLE ? CHECK CONSTRAINT ALL
    EXEC sp_MSForEachTable ALTER TABLE ? ENABLE TRIGGER ALL
    EXEC sp_MSFOREACHTABLE SELECT * FROM ?
    "
  • #727197
    Hi,

    To disable a foreign key constraint, you can do in two ways

    Way#1 Using SQL Server Management Studio
    ---------------------------------------------------------

    1. In Object Explorer, expand the table with the constraint and then expand the Keys folder.

    2. Right-click the constraint and select Modify.

    3. In the grid under Table Designer, click Enforce Foreign Key Constraint and select No from the drop-down menu and finally click Close.

    Way#2 Using Command
    ------------------------------------

    Open the query window and execute the following
    statement

    USE <Your Database Name>;
    GO
    ALTER TABLE <Your TableName>
    NOCHECK CONSTRAINT <Your ForeignKey ConstraingName>;
    GO

  • #727203
    Hai Mahesh,
    I want to use the second method that you suggested.but i did n't know how can i get the 'ForeignKey ConstraingName'?

  • #727211
    select the table in query window, and press the key Alt + F1. You will be able to see all the details ablout the table.

    Or

    In the objects explorer, Expand Database -> Tables -> Expand your table name -> Expand Keys

    Regards,
    Manick

  • #727262
    In this response I am posting guideline of How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements.Do like this
    [1]in Server Explorer, select the table constraint, and from of the Database menu -> Open Table Definition.
    [2]In the Table Designer click on Relationships
    [3] After above Foreign Key Relationships dialog box appears then Selecte Relationship list.
    I refer to below links
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7baf5162-4ce5-419b-b508-a8e4c0d7e5cb/how-to-disable-the-foreign-key-constraint-and-enable-after-delete-insert-and-update-the-table

  • #730273
    Hi,


    Alter table <Tablename> NocheckConstraint <constraint_name>

    Alter table <TableName> Drop Constraint <Constraint_name>

    hope this will help


    Sriram.R


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.