Postgres 13 | db<>fiddle

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) );

✓

hidden batch(es)

INSERT INTO users(name) VALUES ('Alice'), ('Bob'); INSERT INTO notes(user_id, content) VALUES (1, 'Alice Note 1'), (1, 'Alice Note 2'), (2, 'Bob Note');

2 rows affected

3 rows affected

hidden batch(es)

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".

hidden batch(es)