An ALTER TABLE statement can be used to add a foreign key to a table. Although typically, the foreign key is added during a CREATE TABLE statement, there are instances where it can be beneficial to add foreign keys after the table has been created and the data has been loaded.
When we use a CREATE TABLE statement, we do not have to define the foreign key constraint name as PostgreSQL and other databases will assign an auto-generated name. The basic syntax to add a foreign key constraint to an existing table looks like this:
ALTER TABLE <childtablename> ADD CONSTRAINT <constraintname> FOREIGN KEY (<fk_column>) REFERENCES <parenttablename> (<parent_key_column>);
Given the following tables:
CREATE TABLE representative ( representative_id INT PRIMARY KEY, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL ); CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR (100) NOT NULL, manager_id INT );
We can add a foreign key to the department table for the manager_id by doing:
ALTER TABLE department ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES representative (representative_id);
It is not that different than the syntax that is used directly in the CREATE TABLE statement but the ordering of the statements is important.
With the ALTER TABLE statements, there can be other additional clauses that can be set. By default, they are set to “no action” meaning nothing is attempted if the parent record is deleted. We can add ON DELETE SET NULL which will set the value to null in the child table if the parent record is deleted. This means that the foreign key column would have to allow NULL values to be used.
We can also use the ON DELETE SET DEFAULT clause and pass in a specific value. This means that if the parent record is deleted, the child records would have a set value to be updated instead. For example, if a manager is deleted, the employees could be automatically be moved under a specific generic employee to be reallocated later on.
Another clause can be the ON DELETE CASCADE. This clause can be a dangerous one but it is one of the most common uses. Using this clause, a delete on a parent record would also delete all of the referencing rows in the child table. For example, let us assume we used our database and we had used the ON DELETE CASCADE clause on the support_rep_id in the customer table. If this was the case and we deleted employee_id 1 from the employee table, rather than throwing an error due to the foreign key constraint, it would instead attempt to delete all of the customer records that had the support_rep_id. Subsequently, if we used ON DELETE CASCADE on all of the foreign keys of all of our tables, the database would then try to delete all the invoices that reflected those customers that had the support_rep_id equal to 1. Then it would also delete the invoice_line records that reflect the deleted invoices.
You can quickly see why this could create a lot of potential issues in the database if we were not careful. If we wanted to add these additional clauses to the table, we would have to first drop the constrain:
ALTER TABLE department DROP CONSTRAINT fk_manager;
Then we can add it back on with any changes to the clauses:
ALTER TABLE department ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES representative (representative_id) ON DELETE CASCADE;
Source: Authored by Vincent Tran