Add on question to Cascade delete

[c***@hotmail.com]

If I did not define a cascade delete on the child referential constraint.
When delete parent record, will it delete related child record automatically?
What is the default value? of referential constraint?
Thanks.

[Mark A]

Post by c***@hotmail.com
If I did not define a cascade delete on the child referential constraint.
When delete parent record, will it delete related child record
automatically?
What is the default value? of referential constraint?
Thanks.

If there is a foreign key on a child table defined as "on delete restrict"
or "on delete no action" pointing back to a parent table, and you try delete
a row on the parent table that has a corresponding value on one or more rows
on the child table, you will get an error and the delete will fail. To
automatically delete the children rows when the parent is deleted, the FK
constraint must be defined as "delete cascade".

The default is "no action", but it is very similar to restrict and only
affects the timing of the check in certain situations:

"A delete or update rule of RESTRICT is enforced before all other
constraints, including those referential constraints with modifying rules
such as CASCADE or SET NULL. A delete or update rule of NO ACTION is
enforced after other referential constraints."

[Knut Stolze]

Post by Mark A
"A delete or update rule of RESTRICT is enforced before all other
constraints, including those referential constraints with modifying rules
such as CASCADE or SET NULL. A delete or update rule of NO ACTION is
enforced after other referential constraints."

Here is a simple example that illustrates just this difference:

CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY);
INSERT INTO t1 VALUES (1), (2);

CREATE TABLE t2 (
a int,
FOREIGN KEY (a) REFERENCES t1 ON UPDATE NO ACTION );
INSERT INTO t2 VALUES(2);

UPDATE t1 SET a = a + 1;

The last statement works with ON UPDATE NO ACTION, but fails with ON UPDATE
RESTRICT. As Mark explained: the update is completed and all references are
correct _after_ the update. Since NO ACTION checks after the update,
things are fine; while RESTRICT checks before the update, finds the
dependent rows and prevents the update from happening in the first place.