[Vultra]

Hello all,

got a question, what would happen if you don't use "ON DELETE CASCADE" in a mysql database? Like, what is the consequence of not doing so if you're wanting to remove a table or row?

thanks,

- Mimi

[mothered]

Are you using the SQL FOREIGN KEY constraint? When you delete data from the parent table, the same will be deleted In the child table.

If you wish to delete a table, simply use the SQL DROP statement. To delete a column row, use the SQL DELETE statement and the SQL WHERE clause. The latter defines what column row(s) you wish to delete.

[Hoss]

mothered explained it pretty well. I'd also like to add that "ON DELETE CASCADE" is very rarely the best option. It causes way more issues than it solves especially with people inexperienced with schema design.

[Vultra]

mothered explained it pretty well. I'd also like to add that "ON DELETE CASCADE" is very rarely the best option. It causes way more issues than it solves especially with people inexperienced with schema design.

Now that is a question I would like to talk to the teacher about.

[mothered]

I'd also like to add that "ON DELETE CASCADE" is very rarely the best option. It causes way more issues than it solves

I try and avoid using It.

When using the SQL PRIMARY KEY & FOREIGN KEY constraints (example) between two tables, I'd rather use the ON UPDATE CASCADE clause when adding the FOREIGN KEY to the child table, and then delete column rows with the SQL DELETE statement & SQL WHERE clause. To delete multiple column rows In one hit, I specify them using the SQL IN Operator.

[Mansispicher39]

When you have to delete a record in parent table and if it has relations to other records in other tables (aka foreign keys - example subjects to students might be one to many relation aka many students can study one subject -> John and Kate can study math and they are related to that record) and when you try to delete that subject record the sql wont let you cuz there are still existing relations to this record(John and Kate) so the cascade drop first deletes this 2 students and after that it deletes the Subject.. You can image this situation but with tons of other relations thank god for the ORM-s D

[mothered]

When you have to delete a record in parent table and if it has relations to other records in other tables (aka foreign keys - example subjects to students might be one to many relation aka many students can study one subject -> John and Kate can study math and they are related to that record) and when you try to delete that subject record the sql wont let you cuz there are still existing relations to this record(John and Kate) so the cascade drop first deletes this 2 students and after that it deletes the Subject.. You can image this situation but with tons of other relations thank god for the ORM-s D

Alternatively, you can delete the Foreign key from the child table(s) that corresponds to the Primary key In the parent table, then manipulate the parent table as you please. Then simply add the Foreign key back to the child tables when finished.

It's a little bit of messing around, but gets the job done.

[Mansispicher39]

When you have to delete a record in parent table and if it has relations to other records in other tables (aka foreign keys - example subjects to students might be one to many relation aka many students can study one subject -> John and Kate can study math and they are related to that record) and when you try to delete that subject record the sql wont let you cuz there are still existing relations to this record(John and Kate) so the cascade drop first deletes this 2 students and after that it deletes the Subject.. You can image this situation but with tons of other relations thank god for the ORM-s D

Alternatively, you can delete the Foreign key from the child table(s) that corresponds to the Primary key In the parent table, then manipulate the parent table as you please. Then simply add the Foreign key back to the child tables when finished.

It's a little bit of messing around, but gets the job done.

Yea but in case when you have for example 10 nested relations you have to "manually" one by one remove the FKeys

[mothered]

When you have to delete a record in parent table and if it has relations to other records in other tables (aka foreign keys - example subjects to students might be one to many relation aka many students can study one subject -> John and Kate can study math and they are related to that record) and when you try to delete that subject record the sql wont let you cuz there are still existing relations to this record(John and Kate) so the cascade drop first deletes this 2 students and after that it deletes the Subject.. You can image this situation but with tons of other relations thank god for the ORM-s D

Alternatively, you can delete the Foreign key from the child table(s) that corresponds to the Primary key In the parent table, then manipulate the parent table as you please. Then simply add the Foreign key back to the child tables when finished.

It's a little bit of messing around, but gets the job done.

Yea but in case when you have for example 10 nested relations you have to "manually" one by one remove the FKeys

Agree.

As mentioned, It Is messing around a bit, but you can have your SQL ready and only change the table names and Foreign keys accordingly.
For example:

* Delete the Foreign key from the `User Data` table (child table):

Code:

ALTER TABLE `User Data` DROP FOREIGN KEY `User Data_ibfk_1`;

* Do whatever you wish to the parent table (which Is `User IDs`), and then add the Foreign key back Into child table (which Is `User Data`).  I've used the ON UPDATE & ON DELETE CASCADE clauses for demonstration purposes:

Code:

ALTER TABLE `User Data` ADD FOREIGN KEY (mothered) REFERENCES `User IDs` (mothered) ON UPDATE CASCADE ON DELETE CASCADE;

It's not too time consuming to only alter the table names & respective Foreign keys.

[darkninja1980]

Alternatively, you can delete the Foreign key from the child table(s) that corresponds to the Primary key In the parent table, then manipulate the parent table as you please. Then simply add the Foreign key back to the child tables when finished.

It's a little bit of messing around, but gets the job done.

Yea but in case when you have for example 10 nested relations you have to "manually" one by one remove the FKeys

Agree.

As mentioned, It Is messing around a bit, but you can have your SQL ready and only change the table names and Foreign keys accordingly.
For example:

* Delete the Foreign key from the `User Data` table (child table):

Code:

ALTER TABLE `User Data` DROP FOREIGN KEY `User Data_ibfk_1`;

* Do whatever you wish to the parent table (which Is `User IDs`), and then add the Foreign key back Into child table (which Is `User Data`).  I've used the ON UPDATE & ON DELETE CASCADE clauses for demonstration purposes:

Code:

ALTER TABLE `User Data` ADD FOREIGN KEY (mothered) REFERENCES `User IDs` (mothered) ON UPDATE CASCADE ON DELETE CASCADE;

It's not too time consuming to only alter the table names & respective Foreign keys.

I totally agree