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.

JohnD · Answer 1 · 2013-05-28T13:01:21.350

22

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

edited May 28 '13 at 13:01

answered May 28 '13 at 12:50

JohnD

14,327
4
40
53

I don't know the fk_name. Can you please update it by seeing mine query above – Imran Qadir Baksh - Baloch May 28 '13 at 12:52
You can pick the name of the FK, I chose FK_Products_Brands as an example. – JohnD May 28 '13 at 13:01
Is this is not possible without name? Thanks anyway – Imran Qadir Baksh - Baloch May 28 '13 at 13:04
I think it's good practice to have a naming convention for FKs; I don't know if you can have sql server give a default name. – JohnD May 28 '13 at 13:11
1

If you don't name it SQL will give it some name like `FK__Parent_Table__Columns__39E294A9` – Nenad Zivkovic May 28 '13 at 13:16

score 14 · Accepted Answer · answered May 28 '13 at 13:07

14

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)

answered May 28 '13 at 13:07

Nenad Zivkovic

18,221
6
42
55

Good one. Just want to know that can we use the same query to check if foreign exist then drop it. Please update your answer with this as well. I will update my question a little? – Imran Qadir Baksh - Baloch May 28 '13 at 13:14
1

@user960567 Well, in order to drop the key you need it's name so it might require some dynamic sql. – Nenad Zivkovic May 28 '13 at 14:25
Thanks I am doing the same now. But if you put this in your answer then it will be helpful for future user. – Imran Qadir Baksh - Baloch May 28 '13 at 14:27

score 12 · Answer 3 · answered Sep 26 '13 at 09:25

12

You can use this as well.

IF(OBJECT_ID('FK_Products_Brands', 'F') IS NULL)
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Brands] FOREIGN KEY([BrandID]) REFERENCES [dbo].[Brands] ([Id])

answered Sep 26 '13 at 09:25

Alien_HailToCode

141
1
5

score 7 · Answer 4 · answered Jan 14 '14 at 15:15

To do this without knowing the constraint's name and without inner joins, you can do:

IF NOT EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE [TABLE_NAME] = 'Products' AND [COLUMN_NAME] = 'BrandID')
BEGIN
    ALTER TABLE Products
    ADD FOREIGN KEY (BrandID)
    REFERENCES Brands(ID)
END

If you wanted to, you could get the name of the contraint from this table, then do a drop/add.

score 1 · Answer 5 · answered Sep 26 '22 at 19:59

This worked smoothly for me in Azure sql server.

IF NOT EXISTS (
    SELECT NULL 
    FROM information_schema.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_SCHEMA = 'dbo' AND
    CONSTRAINT_NAME   = 'FK_company_id' AND
    CONSTRAINT_TYPE   = 'FOREIGN KEY'
) ALTER TABLE dbo.table_main
ADD CONSTRAINT FK_company_id
FOREIGN KEY (company_id)
REFERENCES dbo.table_company(id);

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

5 Answers5

Linked