How to Delete a Foreign Key Constraint in SQL

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.