mysql - foreign key constraint allows parent to be deleted -

mysql - foreign key constraint allows parent to be deleted -

i having uncertainty regarding foreign key constraints. made 2 simple table these testing purposes.

mysql> explain parent; +-------+---------+------+-----+---------+----------------+ | field | type | null | key | default | | +-------+---------+------+-----+---------+----------------+ | id | int(11) | no | pri | null | auto_increment | +-------+---------+------+-----+---------+----------------+ 1 row in set (0.01 sec) mysql> explain child; +-----------+---------+------+-----+---------+----------------+ | field | type | null | key | default | | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | no | pri | null | auto_increment | | parent_id | int(11) | no | mul | null | | +-----------+---------+------+-----+---------+----------------+

now did not specify foreign key constraint @ time of creation of table. added later follows.

alter table kid add together constraint parent_fk foreign key(parent_id) references parent(id);

i checked engine used tables , innodb. question are...

why not restricting me deleting record in parent on there several dependent kid records? default should restrict me right?

if create index on foreign key field before add together foreign key constraint, working expected. need create index each time?

is bad practice add together kind of constraints after creation of table?

mysql requires indexes on fields when using alter table add together new foreign key constraints. constraint checked against index in parent table, without index no check made.

14.6.4.4. foreign key constraints

when add together foreign key constraint table using alter table, remember create required indexes first.

and, also:

deviation sql standards: if there several rows in parent table have same referenced key value, innodb acts in foreign key checks if other parent rows same key value not exist. example, if have defined restrict type constraint, , there kid row several parent rows, innodb not permit deletion of of parent rows.

innodb performs cascading operations through depth-first algorithm, based on records in indexes corresponding foreign key constraints.

to reply specific questions:

1) it's not restricting because have not created required indexes. stated in documentation, algo uses indexes corresponding constraint.

2) see #1. yes, unless fk in place before rows added parent table.

3) no, isn't bad practice. need explicit intent. example, want check referential integrity after altering table? here potentially helpful question: force innodb recheck foreign keys on table/tables?

mysql