如何在SQLite / MySQL中限制列值

Question

我想限制表中某一列的取值范围。例如，该列的值只能是car、bike或van。我的问题是如何在SQL中实现这一点，以及在数据库端实现限制还是让应用程序限制输入，哪种方式更好？

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

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

- Maro

Answer 1

添加一个包含这些交通工具的新表格，并将你的列定义为该表格的外键。将来可以向该表格添加新的交通工具，而你的列定义保持不变。

使用这种构造方式，我肯定会选择在数据库层面进行调节，而不是在应用程序层面进行调节。

- NGLN

---

我最喜欢这个选项 - 它是唯一一个能够满足“将来添加更多类型”要求而不需要进行数据库更改（只需插入新行）的答案。 - Bohemian

同意，数据不是静态的这一事实证明它应该是一个查找表而不是一个CHECK约束或等效物。是的，这个数据约束应该在数据库中建模。 - onedaywhen

Answer 2

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

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

查看MySQL参考：ENUM类型

此外，我发现在数据库和应用程序两方面都进行限制总是更好的做法。使用枚举类型加上选择框，问题就解决了。

- nageeb

---

这几乎无法轻松地满足“将来添加更多类型”的要求 - 您必须进行数据库更改。问问数据库管理员他会有多兴奋吧。 - Bohemian

你没有提到你不想打扰可怜的数据库管理员添加更多的项目。或许你应该在问题中明确指出这一点。 - nageeb

我并没有提出问题。我只是在评论你的回答。不过我没有给你点踩 :) - Bohemian

我的错误。如果我的评论听起来有点刻薄，我向您道歉，但我想不同的人对定义有不同的理解。我完全同意被接受的答案（并且已经点赞了它），并认为在编程上允许添加值是最好的解决方案，但作为一名程序员，如果这不是面向最终用户的功能，我个人会直接修改数据库。 - nageeb

当使用ORM层实现时，可以通过迁移轻松更改此设置。通过git进行适当跟踪，我发现这是一种很好的解决方案，可以实现并跟踪数据库本身中实施的值限制。 - Daniel Böttner

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')
);

- Alex Aza

---

在SQLite中，您可以使用check("name" in ('car', 'bike', 'van'))。此外：-引号用于标识符，而' -引号用于字面值。 - equaeghe

Answer 4

您可以使用检查约束。在SQL Server中，它的使用方法如下：

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

我不确定这是否符合ANSI标准，但我确定MySQL有类似的结构。

- Bob Probst

---

缺少一个闭括号吗？ - faizal

Answer 5

如果您想使用数据库端验证，可以使用触发器。对于SQLite，请参见此处，对于MySQL，请参见这个详细的如何操作。

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

另一方面，如果您对数据库没有太多经验，计划针对几个不同的数据库，并且没有时间开发专业知识，则可能简单的应用程序级别验证是最快捷的选择。

- Dilum Ranatunga

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);

然后，可以将“汽车”、“自行车”等插入到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问题的范围，但也要注意，在设置外键约束时，应考虑如果删除或更新父表值（vehicle_types），子表中的列（ops_original_table）会发生什么情况。请参见this page获取更多信息。

- atomscale