Search notes:

Using Oracle syntax for foreign keys in MySQL

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.

I try to demonstrate that odd behavior here.

First, a MySQL database is created:

--  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@'%';

Github repository about-MySQL, path: /objects/table/constraints/foreign-key/Oracle-syntax/create-db.sql

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;

Github repository about-MySQL, path: /objects/table/constraints/foreign-key/Oracle-syntax/create-fk-1.sql

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;

Github repository about-MySQL, path: /objects/table/constraints/foreign-key/Oracle-syntax/insert-values.sql

This behaviour is a bit irritating, I would have at least expected a warning if not an error when the tables were created.

So, in order to have the foreign key enforced, I create it in the traditional way (foreign key (id_p) references p(id)):

--  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,
    txt    varchar(10),
    foreign key (id_p) references p(id)
)
engine innoDB;

Github repository about-MySQL, path: /objects/table/constraints/foreign-key/Oracle-syntax/create-fk-2.sql

Running the insert statements now will reject the record without parent.

Index