Deleting and Updating Data with Foreign Keys in Mind

So, we already know what foreign keys (FOREIGN KEY) are, how they work, and we've even practiced creating tables with them. But what if it's time to delete some data or update related records? Let's figure out how deleting and updating data works together with foreign keys, and what tricks you should know about.

Imagine your database is like a complicated house of cards. If you pull out one card, you might bring the whole thing crashing down. That's where foreign keys come in—they stop your database from "falling apart" when you delete related data. Let's see how this works.

What happens if you try to delete data?

When a table has a foreign key, the DBMS checks if the record is linked to other tables. If it is, trying to delete the record might throw a data integrity error. To avoid surprises, you can set up actions for the foreign key ahead of time. These actions are set using the ON DELETE options.

Setting up behavior with ON DELETE

You can set one of these rules for a foreign key when you create it:

1. ON DELETE CASCADE: Deleting a record in the parent table automatically deletes all related records in the child table.
2. ON DELETE SET NULL: Instead of deleting related records, their foreign keys are set to NULL.
3. ON DELETE SET DEFAULT: The foreign key value is set to its default value.
4. ON DELETE RESTRICT (default behavior): You can't delete the record if there are related records, and you'll get an error.
5. ON DELETE NO ACTION: Pretty much like RESTRICT, but the integrity check is delayed until the transaction ends.

Example: Cascading delete ON DELETE CASCADE

-- Customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- Insert data
INSERT INTO customers (name) VALUES ('Ivan Ivanov');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- Delete customer
DELETE FROM customers WHERE customer_id = 1;

-- Check what happened to the orders table
SELECT * FROM orders; -- No records, they were deleted by cascade!

When we delete a record from the customers table, PostgreSQL automatically deletes all that customer's orders from the orders table.

Example: Setting links to NULL ON DELETE SET NULL

-- Orders table with new behavior rule
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Insert data
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- Delete customer
DELETE FROM customers WHERE customer_id = 1;

-- Check the orders_with_null table
SELECT * FROM orders_with_null;

Result:

With ON DELETE SET NULL we can keep the orders, but "unlink" them from the now non-existent customer.

Updating Data with Foreign Keys

Besides deleting, updating data in parent tables can also affect related records. For example, what happens if a customer changes their customer_id? That's where the ON UPDATE option comes in.

Setting up behavior with ON UPDATE

You can handle changes in the parent table using these strategies:

1. ON UPDATE CASCADE: changing the foreign key value in the parent table automatically updates it in all related records.
2. ON UPDATE SET NULL: the foreign key value in child tables is set to NULL.
3. ON UPDATE SET DEFAULT: sets the default value.
4. ON UPDATE RESTRICT: changing the foreign key value is forbidden if there are related records.
5. ON UPDATE NO ACTION: the check is delayed until the end of the transaction.

Example: Cascading update ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Insert data
INSERT INTO customers_with_cascade (name) VALUES ('Ivan Ivanov');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Change customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Check the orders_with_cascade table
SELECT * FROM orders_with_cascade;

Result:

When you change customer_id, PostgreSQL automatically updates it in the orders_with_cascade table.

Practice: The enrollments Table

Let's remember our example with students and courses. We'll work with the enrollments table enrollments and set up how data deletion is handled.

-- Students table
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Courses table
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Join table for enrollments
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Insert data
INSERT INTO students (name) VALUES ('Aleksei Petrov');
INSERT INTO courses (title) VALUES ('Matematika');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Delete student
DELETE FROM students WHERE student_id = 1;

-- Check the enrollments table
SELECT * FROM enrollments; -- Empty! The record was deleted automatically.

Common Mistakes and How to Avoid Them

One common mistake is trying to delete a record from the parent table without setting up the right foreign key behavior. For example, if you don't specify anything for ON DELETE, the default RESTRICT behavior will kick in, and you'll get an error.

Also, keep in mind that using cascading operations (CASCADE) too often can lead to unexpected results. For example, you might accidentally delete more data than you meant to.

To avoid these problems, follow these tips:

• Always think through your ON DELETE and ON UPDATE behavior based on your app's logic.
• For really important operations, add some check queries before making changes or deletions.
• Use transactions so you can roll back changes if something goes wrong.

2

Task

SQL SELF, level 20, lesson 3

Locked

Delete with Restriction

Delete with Restriction

Open

2

Task

SQL SELF, level 20, lesson 3

Locked

Cascade Delete

Cascade Delete

Open