How to Delete a Foreign Key Constraint in SQL
Database:
Operators:
Table of Contents
Problem
You want to delete a foreign key from a table in a database.
Example
We want to remove the foreign key named fk_student_city_id from the table student.
Solution
ALTER TABLE student DROP CONSTRAINT fk_student_city_id;
Discussion
To delete a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint. In our example, the name of this constraint is fk_student_city_id.
If the constraint for the foreign key was generated by the database, you can find this name in the database. However, each database server has a different way to name constraints. In SQL Server, you can check it by selecting data from sys.key_constraints in the given database. In PostgreSQL, you select the conname column from the pg_constraint table. Oracle stores this data in the user_constraints table, and MySQL allows you to get names from CONSTRAINT_NAME column in information_schema.TABLE_CONSTRAINTS.