如何限制 SQLite / MySQL 中的列值

Question

我想限制表中的列值。例如，列值只能是 car 或 bike 或 van。我的问题是如何在 SQL 中实现这一点，在数据库端执行此操作是一个好主意还是应该让应用程序限制输入？

我还打算在将来添加或删除更多值，例如 truck。

我使用的数据库类型是 SQLite 和 MySQL。

Answer 1

添加一个包含这些交通工具的新表，并使您的列成为该表的外键。将来可以将新的运输方式添加到表中，并且您的列定义保持不变。

通过这种结构，我肯定会选择在数据库级别而不是应用程序级别进行调节。

Answer 2

对于 MySQL，您可以使用 ENUM 数据类型。

column_name ENUM('small', 'medium', 'large')

请参阅 MySQL 参考：ENUM 类型

要添加到此，我发现限制数据库端和应用程序端总是更好。一个枚举加上一个选择框就可以了。

Answer 3

是的，建议添加检查约束。检查约束用于确保数据库中数据的有效性并提供数据完整性。如果它们在数据库级别使用，则使用数据库的应用程序将无法添加无效数据或修改有效数据，从而使数据变得无效，即使应用程序本身接受无效数据也是如此。

在 SQLite 中：

create table MyTable
(
    name string check(name = "car" or name = "bike" or name = "van")
);

在 MySQL 中：

create table MyTable
(
    name ENUM('car', 'bike', 'van')
);

Answer 4

您将使用检查约束。在 SQL Server 中，它是这样工作的，

ALTER TABLE Vehicles
ADD CONSTRAINT chkVehicleType CHECK (VehicleType in ('car','bike','van'));

我不确定这是否是 ANSI 标准，但我确信 MySQL 也有类似的构造。

Answer 5

如果您想进行数据库端验证，可以使用触发器。有关 SQLite，请参阅此，以及此 MySQL 的详细操作方法。

所以问题实际上是您是否应该使用数据库验证。如果您有多个客户端——无论它们是不同的程序，还是多个用户（可能具有不同版本的程序）——那么采用数据库路线绝对是最好的。数据库（希望）是集中式的，因此您可以解耦验证的一些细节。在您的特定情况下，您可以验证插入到列中的值是否包含在仅列出有效值的单独表中。

另一方面，如果您对数据库缺乏经验，计划针对多个不同的数据库，并且没有时间培养专业知识，那么简单的应用程序级验证也许是最方便的选择。

Answer 6

为上面的 @NGLN 的优秀答案添加一些初学者级别的上下文。

首先，需要检查外键约束是否处于活动状态，否则 sqlite 将不会限制引用表的列的输入：

PRAGMA foreign_key;

...给出 0 或 1 的响应，表示打开或关闭。

设置外键约束：

PRAGMA foreign_keys = ON;

需要进行设置以确保 sqlite3 强制执行该约束。

我发现最简单的方法是将引用表的主键设置为类型。在OP的示例中：

CREATE TABLE IF NOT EXISTS vehicle_types(
    vehicle_type text PRIMARY KEY);

然后，可以将“car”、“bike”等插入vehicle_types表（以及将来的更多），并在子表（OP希望在其中的表）的外键约束中引用该表引用车辆的类型）：

CREATE TABLE IF NOT EXISTS ops_original_table(
    col_id integer PRIMARY KEY,
    ...many other columns...
    vehicle_type text NOT NULL,
    FOREIGN KEY (vehicle_type) REFERENCES vehicle_types(vehicle_type);

超出OP问题的范围，但还要注意，在设置外键约束时，应该考虑子表（ops_original_table）中的列会发生什么情况，如果父表值 (vehicle_types) 被删除或更新。请参阅此页面了解信息