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: