4

I want to prevent deleting from parent table when he has children in other tables that.

I make like this

ALTER TABLE constant_det_tb 
ADD CONSTRAINT fk_idparent
FOREIGN KEY (idparent)
REFERENCES constant_tb(id) ON DELETE RESTRICT

When I delete from parent constant_tb table, it delete the rows even the table has reference to another tables and it has records reference to it.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
palAlaa
  • 9,500
  • 33
  • 107
  • 166
  • It enough to create ON DELETE RESTRICT foreign key. If it does not work - please provide full code (SHOW CREATE TABLE for both tables). – Devart Mar 07 '12 at 09:29

2 Answers2

3

Make sure you have InnoDB as storage engine for all affected tables.

Check this (if not already) : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

ON DELETE RESTRICT

reference option is all you need to achieve this.

rkosegi
  • 14,165
  • 5
  • 50
  • 83
0

Checking of foreign keys could be disabled for current session. It can be enabled with

SET FOREIGN_KEY_CHECKS = 1;
David Lopez
  • 353
  • 4
  • 13