How do I drop a foreign key in SQL Server?
Sql ServerTsqlSql 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.