使用外键替换检查约束 [英] Using Foreign Keys to replace check constraint

问题描述

我有一个带有检查约束的表，该表被MySQL忽略.我正在使用外键约束来实现相同的功能.可以，还是在使用此方法时需要了解一些内容?

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)

推荐答案

使用枚举. http://dev.mysql.com/doc/refman/5.0/en /enum.html

这篇关于使用外键替换检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！