如何指定表的字段只能从一组特定值中获取值？

I am not so into database and I have the following problem related the set of values that a field can be have. I am using MySql.

我不是那么进入数据库,我有以下问题涉及一个字段可以有的值集。我正在使用MySql。

I have this DDL table definition:

我有这个DDL表定义:

CREATE TABLE actors (
  id        BigInt(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  sample_id VarChar(128) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `role`    Char(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  wiews     VarChar(16) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  pid       VarChar(16) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `name`    VarChar(128) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  address   VarChar(128) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  country   Char(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci, 
  PRIMARY KEY (
      id
  )
) ENGINE=InnoDB AUTO_INCREMENT=1 ROW_FORMAT=COMPACT DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE actors COMMENT = '';
ALTER TABLE actors ADD CONSTRAINT fk_actors_pgrfas FOREIGN KEY (sample_id)
  REFERENCES pgrfas (sample_id)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION;

I can't change the type of these fields because are used by an application.

我无法更改这些字段的类型,因为应用程序使用了这些字段。

Someone have provided me a specification that says that the role field (that have to be a Char(2)) can take only values in this set: IN('pr','co','br').

有人给我提供了一个规范,说明角色字段(必须是Char(2))只能取这个集合中的值:IN('pr','co','br')。

How can I specify in the previous DDL statment that this field can have only one of these 3 values?

如何在之前的DDL语句中指定此字段只能包含这3个值中的一个?

2 个解决方案

#1

You can use an ENUM

您可以使用ENUM

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time

ENUM是一个字符串对象,其值从允许值列表中选择,这些值在表创建时在列规范中显式枚举

https://dev.mysql.com/doc/refman/5.7/en/enum.html

eg ...

    CREATE TABLE your_table  (
      col1  VARCHAR(40),
      role ENUM('pr','co','br')
  );

#2

MySQL does not provide check constraints. Also, you would need to modify the table to make these an enum (which would otherwise be the best approach in MySQL for a handful of values).

MySQL不提供检查约束。此外,您需要修改表以使这些枚举(否则这将是MySQL中针对少数值的最佳方法)。

But, you can do this with a foreign key reference.

但是,您可以使用外键引用来执行此操作。

create table Roles (
     RoleName char(2) primary key
);

insert into Roles (RoleName)
    values ('pr'), ('co'), ('br');

alter table actors add constraint fk_actors_roles
    foreign key (role) references Roles(RoleName);

#1