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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To remove all the constraints from the DB   SELECT  ALTER TABLE     Table Name     DROP CONSTRAINT     Constraint Name FROM Information Schema CONSTRAINT TABLE USAGE

User · Answer

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

User · Answer

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

User · Answer

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    Delete Primary Keys Delete Foreign Key Relationships

User · Answer

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

User · Answer

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

User · Answer

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  lt TABLE NAME gt  DROP CONSTRAINT  lt FOREIGN KEY NAME gt

User · Answer

Try following  ALTER TABLE  lt TABLE NAME gt  DROP CONSTRAINT  lt FOREIGN KEY NAME gt    Refer   http   www w3schools com sql sql foreignkey asp

User · Answer

alter table  lt referenced table name gt  drop  primary key    Foreign key constraint will be removed

User · Answer

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

[sql-server] How to remove foreign key constraint in sql server?

Examples related to sql-server

Examples related to database

Examples related to foreign-keys