Re: ENUM as FOREIGN KEY

[Tom Krouper]

Same way you'd do it with any other foreign key I think. Here is my test...

mysql> use test
Database changed
mysql> create table gender (id char(1) not null, descrip char(7) not null, primary key(id)) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into gender values ('m','Male'),('f','Female'),('u','Unknown');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE t1 (id tinyint unsigned not null auto_increment, gender_id char(1) NOT NULL, FOREIGN KEY (gender_id) REFERENCES gender(id) ON UPDATE CASCADE oN DELETE CASCADE, primary key(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (NULL,'e');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> insert into t1 values (NULL,'f');
Query OK, 1 row affected (0.00 sec)