2

Off the heels of this question,

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?

5

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.
  • So amazingly awkward. – Evan Carroll May 22 '18 at 20:22
  • 1
    @EvanCarroll, I have added another example with the same root-cause. – Lennart May 23 '18 at 4:04
  • 1
    Nothing with MySQL surprises me. I think pretty soon Oracle will just use their user base to train the attorneys, and serve AdWords in the source code. – Evan Carroll May 23 '18 at 4:06
  • :-) Jokes aside, it will be difficult to fix without breaking existing applications. Perhaps another @@sql_mode = 'USE_ENUM_VALUE', but I think it would be better to add CHECK CONSTRAINTS instead. – Lennart May 23 '18 at 4:15
  • <snide>Yet another reason to eschew Foreign Keys</snide> – Rick James Jun 23 '18 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.