How to remove foreign key constraint in sql server?

Question

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.

possible duplicate of [How do I drop a foreign key in SQL Server?](http://stackoverflow.com/questions/93264/how-do-i-drop-a-foreign-key-in-sql-server) — Liam, Jan 23 '15 at 10:12

score 174 · Accepted Answer · answered Sep 19 '12 at 06:52

174

Try following

ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>

Refer : http://www.w3schools.com/sql/sql_foreignkey.asp

answered Sep 19 '12 at 06:52

Prasanna

4,583
2
22
29

1

I haven't added any constraint name. how do I delete that constraint? – Abhishek Goel Dec 01 '13 at 09:15
1

Even if you don't specify the constrain name SQL Server will assign a unique constrain name. You can find the name with SQL Management Studio. – Edward Olamisan Aug 15 '14 at 15:58
3

@Nytux The OP clearly stated that the platform is MS SQL Server, not PostgreSQL (tags and question title). – Alexandre Apr 17 '17 at 20:49

CloudyMarble · Answer 2 · 2013-04-16T10:43:09.487

15

Its wrong to do that in refer to referential integrity, 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:

edited Apr 16 '13 at 10:43

answered Sep 19 '12 at 06:51

CloudyMarble

36,908
70
97
130

No, the syntax in SQL Server for dropping Foreign Key and Primary Key are the same: alter table drop constraint – demoncodemonkey Apr 16 '13 at 10:26
@demoncodemonkey ContName or should be the column name in the existing table or the column name from the original table which we are using as a foreign key in the current one? – iatharva Jan 19 '22 at 05:06
@iatharva it's the name of the constraint - for example PK_Users_Id or FK_Users_Id etc. You can view all the foreign keys in your database using `SELECT name FROM sys.foreign_keys`. Good luck! – demoncodemonkey Jan 19 '22 at 14:31

Simon Wang · Answer 3 · 2016-06-02T00:13:59.313

6

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

edited Jun 02 '16 at 00:13

answered Sep 19 '12 at 06:52

Simon Wang

2,843
1
16
32

1

He can just add the constraint again if he wants it back, as long as the referential integrity is still there. If it isn't, that has to be fixed anyway. – Tobberoth Dec 04 '14 at 10:08
@Tobberoth, yes, that what I meant, thanks for made it clear. In the real world most of the time some other developer will mess up the data in the table and you can't add the constraint back due to those data there. – Simon Wang Dec 04 '14 at 20:59

score 5 · Answer 4 · edited Mar 11 '16 at 15:38

5

To remove all the constraints from the DB:

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

edited Mar 11 '16 at 15:38

tom

15
9

answered Feb 24 '15 at 18:41

Sakthivel Murugesan

71
1
1

score 3 · Answer 5 · answered Feb 07 '16 at 17:30

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.)

score 3 · Answer 6 · answered Jan 11 '18 at 14:55

Drop all the foreign keys of a table:

USE [Database_Name]
DECLARE @FOREIGN_KEY_NAME VARCHAR(100)

DECLARE FOREIGN_KEY_CURSOR CURSOR FOR
SELECT name FOREIGN_KEY_NAME FROM sys.foreign_keys WHERE parent_object_id = (SELECT object_id FROM sys.objects WHERE name = 'Table_Name' AND TYPE = 'U')

OPEN FOREIGN_KEY_CURSOR
----------------------------------------------------------
FETCH NEXT FROM FOREIGN_KEY_CURSOR INTO @FOREIGN_KEY_NAME
WHILE @@FETCH_STATUS = 0
    BEGIN
       DECLARE @DROP_COMMAND NVARCHAR(150) = 'ALTER TABLE Table_Name DROP CONSTRAINT' + ' ' + @FOREIGN_KEY_NAME

       EXECUTE Sp_executesql @DROP_COMMAND

       FETCH NEXT FROM FOREIGN_KEY_CURSOR INTO @FOREIGN_KEY_NAME

    END
-----------------------------------------------------------------------------------------------------------------
CLOSE FOREIGN_KEY_CURSOR
DEALLOCATE FOREIGN_KEY_CURSOR

you are wrong with **parent_object_id** ... it should be used instate of that **referenced_object_id** — udoline, Nov 21 '19 at 14:24

score 2 · Answer 7 · answered Oct 22 '17 at 11:16

Depending on the DB you are using there's a syntax or another.

If you're using Oracle you have to put what the other users told you:

ALTER TABLE table_name DROP CONSTRAINT fk_name;

But if you use MySQL then this will give you a syntax error, instead you can type:

ALTER TABLE table_name DROP INDEX fk_name;

Jay Adikary · Answer 8 · 2019-12-27T05:29:22.910

2

firstly use

show create table table_name;

to see the descriptive structure of your table.

There you may see constraints respective to foreign keys you used in that table. First delete the respective constraint with

alter table table_name drop constraint constraint_name;

and then delete the respective foreign keys or column you wanted...GoodLuck!!

edited Dec 27 '19 at 05:29

answered Dec 27 '19 at 05:06

Jay Adikary

21
4

score 1 · Answer 9 · answered Sep 19 '12 at 06:51

1

ALTER TABLE table
DROP FOREIGN KEY fk_key

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

ALTER TABLE table
DROP CONSTRAINT fk_key

answered Sep 19 '12 at 06:51

mokuril

742
2
9
14

score 1 · Answer 10 · answered Oct 29 '15 at 23:37

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

score 1 · Answer 11 · answered Jan 13 '17 at 00:24

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.

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

I hope that helps

score 1 · Answer 12 · answered Aug 02 '20 at 18:46

If you find yourself in a situation where the FK name of a table has been auto-generated and you aren't able to view what it exactly is (in the case of not having rights to a database for instance) you could try something like this:

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);
SELECT @table = N'dbo.Table';
SELECT @sql = 'ALTER TABLE ' + @table
    + ' DROP CONSTRAINT ' + NAME + ';'
    FROM sys.foreign_keys
    WHERE [type] = 'F'
    AND [parent_object_id] = OBJECT_ID(@table);
EXEC sp_executeSQL @sql;

Build up a stored proc which drops the constraint of the specified table without specifying the actual FK name. It drops the constraint where the object [type] is equal to F (Foreign Key constraint).

Note: if there are multiple FK's in the table it will drop them all. So this solution works best if the table you are targeting has just one FK.

score 0 · Answer 13 · answered Nov 22 '19 at 20:59

0

If you don't know foreign key constraint name then try this to find it.

sp_help 'TableName'

additionally for different schema

sp_help 'schemaName.TableName'

then

ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>

answered Nov 22 '19 at 20:59

Mashood Murtaza

477
5
14

score 0 · Answer 14 · answered Apr 09 '20 at 12:55

0

To be on the safer side, just name all your constraints and take note of them in the comment section.

ALTER TABLE[table_name]
DROP CONSTRAINT Constraint_name

answered Apr 09 '20 at 12:55

Agbesi Innocent

43
8

if you forgot to name your constraint(s), phpMyAdmin gives any of your constraints you used a unique name. To check this given unique name by phpMyAdmin:Click on INFORMATION_SCHEMA tap on the left hand side of your workbench. After this, scroll down and locate TABLE_CONSTRAINTS and click on it. There you will see all the constraints set on the various columns and their names and other details. – Agbesi Innocent Apr 09 '20 at 15:29

score -6 · Answer 15 · edited Sep 26 '16 at 20:22

-6

alter table <referenced_table_name> drop  primary key;

Foreign key constraint will be removed.

edited Sep 26 '16 at 20:22

user353gre3

2,747
4
24
27

answered Sep 26 '16 at 13:15

user6882476

1

Dropping the referenced table's primary key is perhaps the worst way to try fixing the issue. Oh wait - dropping the referenced table itself might be worse. – brewmanz Dec 18 '16 at 19:54
This avoids the essence of the question posed – Spencer Sullivan Mar 15 '19 at 18:30

How to remove foreign key constraint in sql server?

15 Answers15

Linked