Setting up table relations what do "Cascade", "Set Null" and "Restrict" do?

Question

I want to start using table relations in a new project.

After some googling I got 2 tables set up as InnoDB:

The keys I want to link are

->users->userid (primary) ->sessions->userid (index)

The only thing that I don't understand in this process is what the different settings for "On update" and "On delete" do

The options here are:

-- (nothing?)
Cascade (???)
Set Null (sets everything to null?)
No action (well duh...)
Restrict (???)

I basically want the data in sessions to be deleted when a user is completely deleted This since the sessions will only be deleted when the expiration is detected by my session manager...

So if anyone can tell me what these options do it would be much appreciated.

Ted Hopp · Accepted Answer · 2014-07-10T18:07:58.947

CASCADE will propagate the change when the parent changes. (If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.)

SET NULL sets the column value to NULL when a parent row goes away.

RESTRICT causes the attempted DELETE of a parent row to fail.

EDIT: You didn't ask about them, but the SQL standard defines two other actions: SET DEFAULT and NO ACTION. In MySQL, NO ACTION is equivalent to RESTRICT. (In some DBMSs, NO ACTION is a deferred check, but in MySQL all checks are immediate.) The MySQL parser accepts SET DEFAULT, but both the InnoDB and NDB engines reject those statements, so SET DEFAULT can't actually be used for either an ON UPDATE or ON DELETE constraint.

Also, note that cascading foreign key actions do not activate triggers in MySQL.

Thank you Ted, I've set it to On Delete: Cascade and On Update: Do Nothing and tested it, seems like it does exactly want I want it to do — HTDutchy, Mar 21 '11 at 21:11

score 26 · Answer 2 · edited Jun 20 '20 at 09:12

The table containing the foreign key is called the referencing or child table, and the table containing the candidate key is called the referenced or parent table.

Set NULL : Sets the column value to NULL when you delete the parent table row.

CASCADE : CASCADE will propagate the change when the parent changes. If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.

RESTRICT : RESTRICT causes you can not delete a given parent row if a child row exists that references the value for that parent row.

NO ACTION : NO ACTION and RESTRICT are very much alike. when an UPDATE or DELETE statement is executed on the referenced table, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. in short child row no concern if parent row delete or update.

score 0 · Answer 3 · edited Feb 10 '23 at 10:22

The three main types of referential actions for foreign key constraints in SQL are Cascade, Set Null and Restrict.

CASCADE: When a record in the parent table is deleted, any related records in the child table will also be deleted.

Example:

CREATE TABLE orders (
  order_id INT NOT NULL,
  customer_id INT NOT NULL,
  PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
);

SET NULL: When a record in the parent table is deleted, any related records in the child table will be set to NULL.

Example:

CREATE TABLE orders (
  order_id INT NOT NULL,
  customer_id INT,
  PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE SET NULL
);

RESTRICT: When a record in the parent table is deleted, an error will be thrown and the deletion will be prevented.

Example:

CREATE TABLE orders (
  order_id INT NOT NULL,
  customer_id INT NOT NULL,
  PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE RESTRICT
);

Setting up table relations what do "Cascade", "Set Null" and "Restrict" do?

3 Answers3

Linked

Related