CODEWITHSUNDEEP
sql-servert-sql
mmattax
mmattax

Reputation: 27670

How do I drop a foreign key in SQL Server?

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.

Upvotes: 243

Views: 404605

Answers (8)

Mike
Mike

Reputation: 5281

Try

alter table company drop constraint Company_CountryID_FK


alter table company drop column CountryID

Upvotes: 366

Naeem Iqbal
Naeem Iqbal

Reputation: 405

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

Upvotes: 24

Samir Savasani
Samir Savasani

Reputation: 348

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.

Upvotes: 29

Philip Wade
Philip Wade

Reputation: 358

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.

Upvotes: 2

Dave Costa
Dave Costa

Reputation: 48111

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

alter table company drop **constraint** Company_CountryID_FK;

Upvotes: 4

boes
boes

Reputation: 2855

alter table company drop constraint Company_CountryID_FK

Upvotes: 11

Gerardo Jaramillo
Gerardo Jaramillo

Reputation: 485

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

Upvotes: 1

Jared
Jared

Reputation: 8600

This will work:

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

Upvotes: 52

Related Questions