I have a table with check constraint that is ignored by MySQL. I am using foreign key constraint to achieve the same functionality. Is it OK or if there is something I need to know while I use this method?
CREATE TABLE country (
code VARCHAR(2) NOT NULL,
iso_code VARCHAR(3) NOT NULL,
name VARCHAR(30) NOT NULL,
status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
PRIMARY KEY (code),
CONSTRAINT CK_COUNTRY_STATUS CHECK(status in ('ACTIVE','INACTIVE'))
)ENGINE=InnoDB
drop table if exists country_status;
CREATE TABLE country_status (status varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (status)) ENGINE=InnoDB ;
insert into country_status VALUES ('ACTIVE') , ('INACTIVE');
alter table country add CONSTRAINT FK_COUNTRY_STATUS FOREIGN KEY (status) REFERENCES country_status (status);
mysql> show create table country\G
*************************** 1. row ***************************
Table: country
Create Table: CREATE TABLE `country` (
`code` varchar(2) NOT NULL,
`iso_code` varchar(3) NOT NULL,
`name` varchar(30) NOT NULL,
`status` varchar(10) NOT NULL DEFAULT 'ACTIVE',
PRIMARY KEY (`code`),
KEY `FK_COUNTRY_STATUS` (`status`),
CONSTRAINT `FK_COUNTRY_STATUS` FOREIGN KEY (`status`) REFERENCES `country_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
