How do I drop a foreign key in SQL Server

Question

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

User · Answer

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

User · Answer

This will work   ALTER TABLE  dbo   company  DROP CONSTRAINT  Company CountryID FK

User · Answer

Try  alter table company drop constraint Company CountryID FK   alter table company drop column CountryID

User · Answer

I don t know MSSQL but would it not be   alter table company drop   constraint   Company CountryID FK

User · Answer

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

User · Answer

Try  alter table company drop constraint Company CountryID FK   alter table company drop column CountryID

User · Answer

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

User · Answer

alter table company drop constraint Company CountryID FK

User · Answer

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

User · Answer

I don t know MSSQL but would it not be   alter table company drop   constraint   Company CountryID FK

User · Answer

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

User · Answer

alter table company drop constraint Company CountryID FK

User · Answer

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

User · Answer

alter table company drop constraint Company CountryID FK

User · Answer

This will work   ALTER TABLE  dbo   company  DROP CONSTRAINT  Company CountryID FK

User · Answer

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

User · Answer

Try  alter table company drop constraint Company CountryID FK   alter table company drop column CountryID

User · Answer

I don t know MSSQL but would it not be   alter table company drop   constraint   Company CountryID FK

User · Answer

I don t know MSSQL but would it not be   alter table company drop   constraint   Company CountryID FK

User · Answer

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

User · Answer

Try  alter table company drop constraint Company CountryID FK   alter table company drop column CountryID

User · Answer

This will work   ALTER TABLE  dbo   company  DROP CONSTRAINT  Company CountryID FK

User · Answer

This will work   ALTER TABLE  dbo   company  DROP CONSTRAINT  Company CountryID FK

User · Answer

alter table company drop constraint Company CountryID FK

[sql-server] How do I drop a foreign key in SQL Server?

Examples related to sql-server

Examples related to tsql