Updating & Removing SQL Foreign Key Constraints

index.md

Updating & Removing SQL Foreign Key Constraints

Besides adding foreign key constraints at creation time (i.e. when using CREATE TABLE), we can also add such constraints after a table has been created with help of ALTER TABLE. We can also remove foreign key constraints from an existing table.

Adding Foreign Key Constraints Via ALTER TABLE

MySQL & PostgreSQL

ALTER TABLE <table_name>
ADD FOREIGN KEY <column_name> REFERENCES <related_table> (<related_column>) ON DELETE ... ON UPDATE...

Removing Foreign Key Constraints Via ALTER TABLE

MySQL & PostgreSQL

ALTER TABLE <table_name>
DROP FOREIGN KEY <constraint_name>;

In order to DROP a foreign key constraint (just as for dropping any other kind of constraint), we need to assign a name to the constraint when adding it.

This can be done like these:

Adding with constraint name via CREATE TABLE:

PostgreSQL

CREATE TABLE <table_name> (
  <column_name> <column_data_type> FOREIGN KEY <constraint_name> REFERENCES ...
);

MySQL

CREATE TABLE <table_name> (
  <column_name> <column_data_type>, 
  FOREIGN KEY <constraint_name> (<column_name>) REFERENCES <related_table> (<related_column>) ... (see above)
);

The FOREIGN KEY part (i.e. FOREIGN KEY <constraint_name>) is only required if a name should be assigned. If you don't need a name (because you don't plan on dropping the constraint at any time), you can omit FOREIGN KEY.

There also are ways of dropping constraints without names but it's a bit more complicated - see: How to drop SQL default constraint without knowing its name

Adding with constraint name via ALTER TABLE:

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> FOREIGN KEY <column_name> REFERENCES ... (see above)

reference.md

References

Archived from: Schwarzmüller, M., & Lorenz, M. (2021, November 17). Updating & Removing Foreign Key Constraints. SQL - The Complete Developer's Guide (MySQL, PostgreSQL). Retrieved January 4, 2023, from https://www.udemy.com/course/sql-the-complete-developers-guide-mysql-postgresql/learn/lecture/28877276