By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335961 fiddles created (27479 in the last week).
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE notes (
id SERIAL PRIMARY KEY,
user_id INT,
content TEXT,
CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id)
);
SELECT notes.*, users.name
FROM notes
INNER JOIN users
ON notes.user_id = users.id
id
user_id
content
name
1
1
Alice Note 1
Alice
2
1
Alice Note 2
Alice
3
2
Bob Note
Bob
…
hidden batch(es)
BEGIN;
UPDATE users SET id=3 WHERE id=1;
UPDATE notes SET user_id=3 WHERE user_id=1;
COMMIT;
ERROR: update or delete on table "users" violates foreign key constraint "fk_user" on table "notes"
DETAIL: Key (id)=(1) is still referenced from table "notes".
