Foreign keys work occasionally in MySQL -

i quite new stackoverflow please sorry in case doing wrong. , sorry english - it's not native language.

ok, make long story short. using:

 server version: 5.6.31 mysql community server (gpl) 

there have 2 tables this:

table texts

create table `texts` (   `id` int(11) unsigned not null auto_increment,   `body` text,   `source_id` int(11) unsigned default null,   primary key (`id`),   key `texts_sources_key` (`source_id`),   constraint `texts_sources_key` foreign key (`source_id`) references `texts` (`id`) on delete set null on update cascade ) engine=innodb auto_increment=5 default charset=utf8 

table texts_sources

create table `texts_sources` (   `id` int(11) unsigned not null auto_increment,   `short_desc` varchar(255) default null,   `long_desc` text,   `url` varchar(255) default null,   primary key (`id`) ) engine=innodb auto_increment=2 default charset=utf8  

as can see - there foreign key linking these tables on delete set null on update cascade.

here initial data:

mysql> select * texts; +----+------+-----------+ | id | body | source_id | +----+------+-----------+ |  1 | ***  |         1 | +----+------+-----------+ 1 row in set (0.00 sec)  mysql> select * texts_sources; +----+------------+-----------+----------------+ | id | short_desc | long_desc | url            | +----+------------+-----------+----------------+ |  1 | *          | null      | http://url.com | +----+------------+-----------+----------------+ 1 row in set (0.00 sec) 

now let's magic.

mysql> update texts_sources set id=5; query ok, 1 row affected (0.05 sec) rows matched: 1  changed: 1  warnings: 0  mysql> select * texts_sources; +----+------------+-----------+----------------+ | id | short_desc | long_desc | url            | +----+------------+-----------+----------------+ |  5 | *          | null      | http://url.com | +----+------------+-----------+----------------+ 1 row in set (0.01 sec)  mysql> select * texts; +----+------+-----------+ | id | body | source_id | +----+------+-----------+ |  1 | ***  |         1 | +----+------+-----------+ 1 row in set (0.00 sec) 

not working. more fun:

mysql> delete texts_sources; query ok, 1 row affected (0.18 sec)  mysql> select * texts; +----+------+-----------+ | id | body | source_id | +----+------+-----------+ |  1 | ***  |         1 | +----+------+-----------+ 1 row in set (0.00 sec) 

not working. ok. maybe not working @ all. let's try way:

mysql> update texts set source_id=5; error 1452 (23000): cannot add or update child row: foreign key constraint fails (`serj_by`.`texts`, constraint `humor_sources_key` foreign key (`source_id`) references `texts` (`id`) on delete set null on update cascade) mysql> insert texts (body, source_id) values ("***", 7); error 1452 (23000): cannot add or update child row: foreign key constraint fails (`serj_by`.`texts`, constraint `humor_sources_key` foreign key (`source_id`) references `texts` (`id`) on delete set null on update cascade) 

working. fun. funnier (texts_sources empty, hope remember):

mysql> insert texts (body, source_id) values ("***", 1); query ok, 1 row affected (0.16 sec)  mysql> select * texts_sources; empty set (0.00 sec)  mysql> select * texts; +----+------+-----------+ | id | body | source_id | +----+------+-----------+ |  1 | ***  |         1 | |  2 | ***  |         1 | +----+------+-----------+ 2 rows in set (0.00 sec) 

so question obvious. was? , how make working in cases expected? highly appreciated. thank in advance!

let's take close @ this:

constraint `texts_sources_key`    foreign key (`source_id`)      references `texts` (`id`) on delete set null on update cascade 

though have named texts_sources_key foreign key references column in same table. intended

constraint `texts_sources_key`    foreign key (`source_id`)      references `texts_sources` (`id`) on delete set null on update cascade 

additionally need create text_sources table first before make constraint.