How do I drop a foreign key in SQL Server?

214
votes

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.

8
sql-servertsql
What error do you get trying to drop the foreign key? – ddc0660
Just be aware that it is dangerous to drop a foreign key constraint without knowing why it is there inthe first place. If you just created this and did it by mistake then use the code provided in the other answers. If not, then do not drop the constraint until you are sure that you won't break something else by doing so. Constraints are created to enforce busness rules and it is better to be certain they are no longer need before dropping them. – HLGEM
w3schools.com/sql/sql_foreignkey.asp – xameeramir
Your syntax for dropping the FK doesn't need the words "foreign key". This is the syntax for MySQL, not SQL Server. You can replace it with the word "constraint". – John Gilmer

8 Answers

331
votes

Try

alter table company drop constraint Company_CountryID_FK


alter table company drop column CountryID
50
votes

This will work:

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

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.

21
votes

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
11
votes
alter table company drop constraint Company_CountryID_FK
4
votes

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

alter table company drop **constraint** Company_CountryID_FK;
1
votes

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

1
votes

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.