If Foreign Key Not Exist Then Add Foreign Key Constraint(Or Drop a Foreign Key Constraint If Exist) without using Name?

Question

I am finding difficulties to creating a a query. Let say I have a Products and Brands table. I can add a foreign key using this command,

          ALTER TABLE Products
          ADD FOREIGN KEY (BrandID)
          REFERENCES Brands(ID)

But I need to only run this command if Foreign Key does not exist. A similar thing I need is that drop a Foreign Key Constraint If Exist without using name.

Answer 1

Try this:

IF NOT EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = object_id(N'[dbo].[FK_Products_Brands]') AND OBJECTPROPERTY(o.object_id, N'IsForeignKey') = 1)
BEGIN
    ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Brands] FOREIGN KEY([BrandID]) REFERENCES [dbo].[Brands] ([Id])
END

Answer 2

First of all, you should always name your FKs and all other constraints in order to save yourself trouble like this.

But, if you don't know the name of FK you can check it using multiple system views:

IF NOT EXISTS 
(
    SELECT * FROM sys.foreign_key_columns fk 
    INNER JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id 
    INNER JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
    WHERE fk.parent_object_id = object_id('Products') AND pc.name = 'BrandID'
    AND fk.referenced_object_id = object_id('Brands') AND rc.NAME = 'ID'
)
ALTER TABLE Products 
ADD CONSTRAINT Your_New_FK_NAME FOREIGN KEY (BrandID)
REFERENCES Brands(ID)