MySQL on delete cascade. Test Example

by

Answer d. is correct, if and only if the storage engine actually supports and enforces foreign key constraints.

If the tables are created with Engine=MyISAM, then neither b. or d. is correct.

If the tables are created with Engine=InnoDB, then d. is correct.

NOTE:

This is true for InnoDB if and only if FOREIGN_KEY_CHECKS = 1; if FOREIGN_KEY_CHECKS = 0, then a DELETE from the parent table (foo) will not remove rows from the child table (foo2) that reference a row removed from the parent table.

Verify this with the output from SHOW VARIABLES LIKE 'foreign_key_checks' (1=ON, 0=OFF)
(The normal default is for this to be ON.)

The output from SHOW CREATE TABLE foo will show which engine the table uses.

The output from SHOW VARIABLES LIKE 'storage_engine' will show the default engine used when a table is created and the engine is not specified.

More Related Contents:

  1. How do I use on delete cascade in mysql?
  2. How do I specify unique constraint for multiple columns in MySQL?
  3. grant remote access of MySQL database from any IP address
  4. Should MySQL have its timezone set to UTC?
  5. JDBC batch insert performance
  6. SQL – Update multiple records in one query
  7. How to find gaps in sequential numbering in mysql?
  8. Can I create view with parameter in MySQL?
  9. MySQL: Invalid use of group function
  10. How to store IPv6-compatible address in a relational database
  11. Scaling solutions for MySQL (Replication, Clustering)
  12. MySQL show status – active or total connections?
  13. MySQL dynamic pivot table
  14. mysql check if numbers are in a comma separated list
  15. mySQL DataSource on Visual Studio 2012
  16. MySQL update case help
  17. Dropping Unique constraint from MySQL table
  18. limiting the rows to where the sum a column equals a certain value in MySQL
  19. Dump a mysql database to a plaintext (CSV) backup from the command line
  20. Mysql select distinct
  21. Mysql permission errors with ‘load data’
  22. How to select column names dynamically in mySQL
  23. How would I use ON DUPLICATE KEY UPDATE in my CodeIgniter model?
  24. MySQL concatenating all columns
  25. Transpose rows into columns in MySQL
  26. MySQL Join Where Not Exists
  27. MYSQL declaring variables
  28. In MySQL what does “Overhead” mean, what is bad about it, and how to fix it?
  29. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  30. MySQL Multiple Joins in one query?

Leave a Comment