How to remove foreign key constraint in sql server?

I want to remove foreign key from another table so i can insert values of my choice.

I am new in databases so please tell me correct sql query to drop or remove foreign key value.

<!-- language: lang-sql -->

ALTER TABLE table
DROP FOREIGN KEY fk_key

EDIT: didn't notice you were using sql-server, my bad

<!-- language: lang-sql -->

ALTER TABLE table
DROP CONSTRAINT fk_key

Try following

ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>

Refer :

[To see links please register here]

Its wrong to do that in refer to [referential integrity][1], because once its broken its not easy to turn it on again without having to go through the records and delete the ones which breaks the constraints.

Anyway the Syntax is as follows:

ALTER TABLE Tablename DROP CONSTRAINT ContName;

See MSDN:

- [Delete Primary Keys][2]
- [Delete Foreign Key Relationships][3]

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

Use those queries to find all FKs:

Declare @SchemaName VarChar(200) = 'Schema Name'
Declare @TableName VarChar(200) = 'Table name'

-- Find FK in This table.
SELECT
'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

'ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName


-- Find the FKs in the tables in which this table is used
SELECT
' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

' ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName

You should consider (temporarily) disabling the constraint before you completely delete it.

If you look at the table creation TSQL you will see something like:

ALTER TABLE [dbo].[dbAccounting] CHECK CONSTRAINT [FK_some_FK_constraint]

You can run

ALTER TABLE [dbo].[dbAccounting] NOCHECK CONSTRAINT [FK_some_FK_constraint]

... then insert/update a bunch of values that violate the constraint, and then turn it back on by running the original `CHECK` statement.

(I have had to do this to cleanup poorly designed systems I've inherited in the past.)

To remove all the constraints from the DB:

SELECT 'ALTER TABLE ' + Table_Name +' DROP CONSTRAINT ' + Constraint_Name
FROM Information_Schema.CONSTRAINT_TABLE_USAGE

ALTER TABLE [TableName] DROP CONSTRAINT [CONSTRAINT_NAME]

But, be careful man, once you do that, you may never get a chance back, and you should read some basic database book see why we need foreign key

alter table <referenced_table_name> drop primary key;

Foreign key constraint will be removed.

Alternatively, you can also delete a Foreign Key Constraint from the SQL Server Management Studio itself. *You can try it if the commands do not work*.

1. Expand your database view.
2. Right Click on Table which has foreign key constraint. Choose Design. A tab with the information about table columns will open.
3. Right click on the column which has the foreign key reference. Or you can right click on any column. Choose Relationships.
4. A list of relationships will appear (if you have one) in a pop up window.
5. From there you can delete the foreign key constraint.

I hope that helps