ON DELETE and ON UPDATE in SQL

The foreign key says the reference must be real. The action says what happens when it stops being real.

CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    parent_id INTEGER REFERENCES comments(id) ON DELETE RESTRICT,
    body TEXT NOT NULL
);

How it works

1. ON DELETE CASCADE removes the children with the parent.
2. ON DELETE SET NULL keeps the child and forgets the link.
3. RESTRICT refuses the delete while any child still points at the row.

Keywords and builtins used here

• CASCADE
• CREATE
• DELETE
• INTEGER
• KEY
• NOT
• NULL
• ON
• PRIMARY
• REFERENCES
• RESTRICT
• SET
• TABLE
• TEXT

The run, in numbers

Lines

7

Characters to type

255

Tokens

51

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 34 seconds.

Type this snippet

Step 1 of 2 in Referential integrity, step 6 of 16 in Schema & constraints.

← Previous Next →