none
drop a foreign key constraint

    Frage

  • I can drop a table if it exists using the following code but do not know how to do the same with a constraint:

    IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName
    go
    I also add the constraint using this code:

    ALTER TABLE [dbo].[TableName] 
      WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
        REFERENCES [dbo].[TableName2] ([ID])
    go
    Dienstag, 5. Februar 2013 07:18

Antworten

  • Hi Joe,

    Try:

    IF EXISTS (SELECT *
      FROM sys.foreign_keys
       WHERE object_id = OBJECT_ID(N'dbo.FK_TableName_TableName2')
       AND parent_object_id = OBJECT_ID(N'dbo.TableName')
    )
      ALTER TABLE [dbo.TableName] DROP CONSTRAINT [FK_TableName_TableName2]


    Iric Wen
    TechNet Community Support

    Mittwoch, 6. Februar 2013 07:10

Alle Antworten

  • Joe,

    If I understood your  question properly then I would like to suggest the way you looked for table exists or not, same way you can confirm that you FK exist or not by the following query.

    SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'FK_TableName_TableName2') AND type = ('F')


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Dienstag, 5. Februar 2013 08:47
  • You can always use MS SQL Server Management Studio to get script for Drop or Create existence object:

    The code will look like this:

    IF  EXISTS (
    	SELECT * FROM sys.foreign_keys 
    	WHERE object_id = OBJECT_ID(N'[dbo].[FK_CONSTRAINTNAME]') 
    	AND parent_object_id = OBJECT_ID(N'[dbo].[TABLENAME]')
    )
    	ALTER TABLE [dbo].[TABLENAME] 
    		DROP CONSTRAINT [FK_CONSTRAINTNAME]
    GO

    • Als Antwort vorgeschlagen Boris Frolov Dienstag, 5. Februar 2013 08:48
    Dienstag, 5. Februar 2013 08:48
  • Hi Joe,

    Try:

    IF EXISTS (SELECT *
      FROM sys.foreign_keys
       WHERE object_id = OBJECT_ID(N'dbo.FK_TableName_TableName2')
       AND parent_object_id = OBJECT_ID(N'dbo.TableName')
    )
      ALTER TABLE [dbo.TableName] DROP CONSTRAINT [FK_TableName_TableName2]


    Iric Wen
    TechNet Community Support

    Mittwoch, 6. Februar 2013 07:10