How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

Question

Off the heels of this question,

• How does MySQL handle joins on ENUMs?

How does MySQL work if the referencing table has more and less values in the ENUM than the table being referenced? Is it validating integrity based solely on the global ENUM textual key (symbol)? Or the underlying integer value?

Answer 1

As demonstrated in the related question, JOINS on enums are "by-value". Constraints, on the other hand, is validated "by-position". This behaviour strikes me as rather counter-intuitive. Using a similar setup as Evan:

CREATE TABLE foo ( a ENUM ('A', 'B') not null primary key );
CREATE TABLE bar ( a ENUM ('X', 'Y', 'Z') not null primary key );
ALTER TABLE bar ADD CONSTRAINT fk_foo_bar 
    FOREIGN KEY (a) REFERENCES foo (a);

insert into foo (a) values ('A'),('B');
insert into bar (a) values ('X'); -- OK
insert into bar (a) values ('Z');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test3"."bar", CONSTRAINT "fk_foo_bar" FOREIGN KEY ("a") REFERENCES "foo" ("a"))

So 'X' is valid according to the foreign key constraint. As Evan demonstrated, JOINs behave differently:

SELECT * FROM foo JOIN bar USING(a);
Empty set (0.00 sec)

So despite that, the row in bar is validated by the foreign key, the join between foo and bar is empty.

It may be tempting at first to use ENUM as a substitute for the lack of CHECK CONSTRAINTS in the MySQL family. However, the way FOREIGN KEYS work together with ENUMs can shoot you in the foot. A typical pattern for subtypes look like:

CREATE TABLE P 
( p int not null primary key
, sub_type ENUM ('A','B')
,     constraint AK_P unique (p, sub_type));

CREATE TABLE A 
( p int not null primary key
, sub_type ENUM ('A')
,     constraint aaa foreign key (p, sub_type) 
                     references P (p, sub_type));

CREATE TABLE B 
( p int not null primary key
, sub_type ENUM ('B')
,     constraint bbb foreign key (p, sub_type) 
                     references P (p, sub_type));

insert into P (p,sub_type) values (1,'A'),(2,'B');
insert into A (p,sub_type) values (1,'A'); -- OK
insert into B (p,sub_type) values (1,'B'); -- OK, but should not be allowed since 1 is sub_type 'A' in parent
insert into B (p,sub_type) values (2,'B'); -- Fails, but should be OK

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test3"."B", CONSTRAINT "bbb" FOREIGN KEY ("p", "sub_type") REFERENCES "P" ("p", "sub_type"))

So the foreign key validates the position in the enum, not the value itself.

PostgreSQL does not allow foreign keys between different enums, so the situation is not possible to reproduce there:

CREATE type e1 as ENUM ('A', 'B');
CREATE type e2 as ENUM ('X', 'Y', 'Z');

CREATE TABLE foo ( a e1 not null primary key );
CREATE TABLE bar ( a e2 not null primary key );

ALTER TABLE bar ADD CONSTRAINT fk_foo_bar 
    FOREIGN KEY (a) REFERENCES foo (a); 
ERROR:  foreign key constraint "fk_foo_bar" cannot be implemented
DETAIL:  Key columns "a" and "a" are of incompatible types: e2 and e1.