CODEWITHSUNDEEP
mysqlsqlforeign-keyssqlcommandcascading-deletes
William Roeser
William Roeser

Reputation: 67

"On Delete Cascade" if deleting a row from the child table

I'm having a difficult time understanding "on delete cascade" If I had the following example:

create table X (id int primary key, name char(10));

create table Y (bid int primary key, aid references A(id) on delete cascade);

X contains one row (111, 'Mike')

Y contains two rows (1000, 111), (2000, 111)**

I if removed row (2000,111) in table Y what would happen?

Would that row just be deleted or would it even allow me to delete anything because of the reference to the parent table?

Thanks

Upvotes: 5

Views: 4494

Answers (3)

Declan_K
Declan_K

Reputation: 6826

It would be deleted and nothing else would happen. Cascading deletes only go from the referenced table to the referencing table. So a delete on table X will cascade a delete down to table y, while a delete on table y has no impact on table x.

Upvotes: 7

Sonam
Sonam

Reputation: 3466

ON Delete cascade option wont effect anything if you perform any delete on the child table. This option is used to specify, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

Nothing will happen, only if you delete a row from table X the rows in table Y referencing it will be deleted.

Upvotes: 2

Related Questions