How do I drop a foreign key in SQL Server?

Sql ServerTsql

Sql Server Problem Overview


I have created a foreign key (in SQL Server) by:

alter table company add CountryID varchar(3);
alter table company add constraint Company_CountryID_FK foreign key(CountryID) 
references Country;

I then run this query:

alter table company drop column CountryID;

and I get this error:

> Msg 5074, Level 16, State 4, Line 2
The object 'Company_CountryID_FK' is dependent on column 'CountryID'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE DROP COLUMN CountryID failed because one or more objects access this column

I have tried this, yet it does not seem to work:

alter table company drop foreign key Company_CountryID_FK; 
alter table company drop column CountryID;

What do I need to do to drop the CountryID column?

Thanks.

Sql Server Solutions


Solution 1 - Sql Server

Try

alter table company drop constraint Company_CountryID_FK


alter table company drop column CountryID

Solution 2 - Sql Server

This will work:

ALTER TABLE [dbo].[company] DROP CONSTRAINT [Company_CountryID_FK]

Solution 3 - Sql Server

I think this will helpful to you...

DECLARE @ConstraintName nvarchar(200)
SELECT 
	@ConstraintName = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
	KCU.TABLE_NAME = 'TABLE_NAME' AND
	KCU.COLUMN_NAME = 'TABLE_COLUMN_NAME'
IF @ConstraintName IS NOT NULL EXEC('alter table TABLE_NAME drop  CONSTRAINT ' + @ConstraintName)

It will delete foreign Key Constraint based on specific table and column.

Solution 4 - Sql Server

First check of existence of the constraint then drop it.

if exists (select 1 from sys.objects where name = 'Company_CountryID_FK' and type='F')
begin
alter table company drop constraint  Company_CountryID_FK
end

Solution 5 - Sql Server

alter table company drop constraint Company_CountryID_FK

Solution 6 - Sql Server

I don't know MSSQL but would it not be:

alter table company drop **constraint** Company_CountryID_FK;

Solution 7 - Sql Server

You can also Right Click on the table, choose modify, then go to the attribute, right click on it, and choose drop primary key.

Solution 8 - Sql Server

Are you trying to drop the FK constraint or the column itself?

To drop the constraint:

alter table company drop constraint Company_CountryID_FK

You won't be able to drop the column until you drop the constraint.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionmmattaxView Question on Stackoverflow
Solution 1 - Sql ServerMikeView Answer on Stackoverflow
Solution 2 - Sql ServerJaredView Answer on Stackoverflow
Solution 3 - Sql ServerSamir SavasaniView Answer on Stackoverflow
Solution 4 - Sql ServerNaeem IqbalView Answer on Stackoverflow
Solution 5 - Sql ServerboesView Answer on Stackoverflow
Solution 6 - Sql ServerDave CostaView Answer on Stackoverflow
Solution 7 - Sql ServerGerardo JaramilloView Answer on Stackoverflow
Solution 8 - Sql ServerPhilip WadeView Answer on Stackoverflow