none
Re-enable Foregin Key constraint still shows NOCHECK ? RRS feed

  • Question

  • Hello I disabled a Foreign Key constraint within a table like:

    ALTER TABLE [dbo].[TableName_1] NOCHECK CONSTRAINT [ForeignKeyName_FK1]

    But when I ran the following to re-enable it did complete successfully...

    ALTER TABLE [dbo].[TableName_1] CHECK CONSTRAINT [ForeignKeyName_FK1]

    But when I script out this Key as Create New in trying to validate comes out as:

    ALTER TABLE [dbo].[TableName_1]  WITH NOCHECK ADD  CONSTRAINT [ForeignKeyName_FK1] FOREIGN KEY([Import_Seq_Nbr])
    REFERENCES [dbo].[TableName_2] ([ColumnName])
    GO

    ALTER TABLE [dbo].[TableName_1] CHECK CONSTRAINT [ForeignKeyName_FK1]
    GO

    How come it still shows NOCHECK instead of CHECK?

    Thanks in advance.

    Thursday, December 12, 2019 8:41 PM

Answers

  • the key is to check the check constraint.

    try this.. notice the two check's..it is not typo..you have to check the check constraint..

    ALTER TABLE [dbo].[TableName_1] WITH CHECK CHECK CONSTRAINT [ForeignKeyName_FK1]
    GO



    Hope it Helps!!

    Friday, December 13, 2019 12:29 AM

All replies

  • the key is to check the check constraint.

    try this.. notice the two check's..it is not typo..you have to check the check constraint..

    ALTER TABLE [dbo].[TableName_1] WITH CHECK CHECK CONSTRAINT [ForeignKeyName_FK1]
    GO



    Hope it Helps!!

    Friday, December 13, 2019 12:29 AM
  • Ah yes I finally came across that also and looks like need to run that extra statement and is properly re-enabled with TRUSTED status.

    Thanks Stan.

    Friday, December 13, 2019 1:12 AM