Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

Foreign Keys & Altering Tables

Author: Sophia

what's covered
This tutorial explores using the ALTER TABLE command to add a foreign key in two parts:
  1. ALTER TABLE
  2. ON DELETE clause

1. ALTER TABLE

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 when 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 from the syntax used in the CREATE TABLE statement, but the ordering of the statements is important.


2. ON DELETE clause

There are additional clauses that can be set with the ALTER TABLE statement. 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 record would update to a set value. For example, if a manager is deleted, the department could automatically be moved under a specific generic employee and reallocated later on.

The ON DELETE CASCADE clause can be a dangerous one, but it has 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, in our database, let's assume we 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 constraint:


ALTER TABLE department
DROP CONSTRAINT fk_manager;

Then we could 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;


Video Transcript

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
We can use the ALTER TABLE statement to add a foreign key constraint to a table.

Source: Authored by Vincent Tran