How do I drop a foreign key in mssql?

Question

I have created a foreign key (in mssql / tsql) 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.

Answer 1

Try

alter table company drop Company_CountryID_FK


alter table company drop CountryID

Answer 2

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

alter table company drop constraint Company_CountryID_FK;

Answer 3

alter table company drop constraint Company_CountryID_FK

Answer 4

This will work:

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

Answer 5

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.

Answer 6

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