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.
This question is related to
sql-server
tsql
alter table company drop constraint Company_CountryID_FK
This will work:
ALTER TABLE [dbo].[company] DROP CONSTRAINT [Company_CountryID_FK]
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.
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.
This will work:
ALTER TABLE [dbo].[company] DROP CONSTRAINT [Company_CountryID_FK]
You can also Right Click on the table, choose modify, then go to the attribute, right click on it, and choose drop primary key.
I don't know MSSQL but would it not be:
alter table company drop **constraint** Company_CountryID_FK;
This will work:
ALTER TABLE [dbo].[company] DROP CONSTRAINT [Company_CountryID_FK]
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
alter table company drop constraint Company_CountryID_FK
I don't know MSSQL but would it not be:
alter table company drop **constraint** Company_CountryID_FK;
This will work:
ALTER TABLE [dbo].[company] DROP CONSTRAINT [Company_CountryID_FK]
alter table company drop constraint Company_CountryID_FK
You can also Right Click on the table, choose modify, then go to the attribute, right click on it, and choose drop primary key.
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.
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
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.
Source: Stackoverflow.com