This is kind of interesting: it's possible to use (what I believe to be) Oracle syntax to create a foreign key - but the foreign key is either not created or enforced.
-- set MYSQL_PWD=iAmRoot
-- "%programfiles%\MySQL\MySQL Server 8.0\bin\mysql.exe" -u root < create-db.sql
drop database if exists fk_tests;
create database fk_tests
character set utf8mb4
collate utf8mb4_0900_as_cs
;
grant all on fk_tests.* to rene@'%';
And and then, I create two tables in a supposed parent-child relationship. Note the id_p … references p(id) part. In Oracle, it specifies that this is a foreign key.
-- set MYSQL_PWD=rene
-- "%programfiles%\MySQL\MySQL Server 8.0\bin\mysql.exe" -u rene fk_tests < create-fk-1.sql
drop table if exists c;
drop table if exists p;
create table p (
id integer primary key,
txt varchar(10)
)
engine innoDB;
create table c (
id_p integer references p(id),
txt varchar(10)
)
engine innoDB;
When run, the two create table statements don't issue any warning and the tables are created.
I am now going to insert a few values into these tables. The last record has no corresonding primary key in the parent table and should be rejected by MySQL. However, the records get inserted just fine:
--
-- "%programfiles%\MySQL\MySQL Server 8.0\bin\mysql.exe" -u rene fk_tests < insert-values.sql
--
delete from c;
delete from p;
set session sql_mode = 'strict_trans_tables,no_engine_substitution,traditional';
insert into p values (1, 'one' );
insert into p values (2, 'two' );
insert into p values (4, 'four');
insert into c values (1, 'foo' );
insert into c values (2, 'bar' );
insert into c values (3, 'baz' );
select * from c;