如何将mysql表列限制为特定值

I was trying to restrict mySql table column like device_type to android and ios. In table structure i set column device_type to enum with values android and ios. But while inserting other like xyz it did not give any error or exception it simply leave column as empty.

我试图将mySql表列限制为device_type到android和ios。在表结构中，我将列device_type设置为enum，其值为android和ios。但是在插入其他像xyz时，它没有给出任何错误或异常，只是将列留空。

Is there any way to restrict column to specific values?

有没有办法将列限制为特定值？

1 个解决方案

#1

I think the best way to approach this is using foreign key constraints. Define a table for device types:

我认为解决这个问题的最佳方法是使用外键约束。定义设备类型的表：

create table DeviceTypes (
    DeviceType varchar(255) primary key
);

insert into DeviceTypes(DeviceType)
     select 'android' union all select 'ios';

Next, you can add a foreign key constraint to enforce the relationship:

接下来，您可以添加外键约束来强制执行关系：

alter table t add constraint fk_t_devicetype foreign key (devicetype) references DeviceTypes(DeviceType);

Attempts to insert values not in the table will result in an error on the insert.

尝试插入不在表中的值将导致插入错误。

Note that in other databases, you can do essentially the same thing with a check constraint. However, MySQL does not enforce those. You can also do essentially the same thing with a trigger, but those can be a maintenance challenge.

请注意，在其他数据库中，您可以使用检查约束执行基本相同的操作。但是，MySQL并没有强制执行这些。您也可以使用触发器执行基本相同的操作，但这些可能是维护挑战。

This method also makes it easy to add new device types.

此方法还可以轻松添加新设备类型。

Also, I would typically use a synthetic key for this purpose (that is, an auto-incremented id), but that isn't necessary to get the functionality you want.

此外，我通常会为此目的使用合成密钥（即自动递增的id），但这不是获得所需功能所必需的。

#1