如何限制 SQLite/MySQL 中的列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6367063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to restrict a column value in SQLite / MySQL
提问by Maro
I would like to restrict a column value in a SQL table. For example, the column values can only be "car" or "bike" or "van". My question is how do you achieve this in SQL, and is it a good idea to do this on the DB side or should I let the application restrict the input.
我想限制 SQL 表中的列值。例如，列值只能是“car”或“bike”或“van”。我的问题是如何在 SQL 中实现这一点，在 DB 端执行此操作是一个好主意，还是应该让应用程序限制输入。
I also have the intention to add or remove more values in the future, for example, "truck".
我也打算在未来添加或删除更多的值，例如“卡车”。
The type of Databases I am using are SQLite and MySQL.
我使用的数据库类型是 SQLite 和 MySQL。
回答by NGLN
Add a new table containing these means of transport, and make your column a foreign key to that table. New means of transport can be added to the table in future, and your column definition remains the same.
添加一个包含这些传输方式的新表，并使您的列成为该表的外键。将来可以将新的传输方式添加到表中，并且您的列定义保持不变。
With this construction, I would definitively choose to regulate this at the DB level, rather than that of the application.
通过这种结构，我肯定会选择在 DB 级别而不是应用程序级别进行管理。
回答by nageeb
For MySQL, you can use the ENUM data type.
对于 MySQL，您可以使用 ENUM 数据类型。
column_name ENUM('small', 'medium', 'large')
列名枚举（'小'，'中'，'大'）
See MySQL Reference: The ENUM Type
To add to this, I find it's always better to restrict on the DB side ANDon the app side. An Enum plus a Select box and you're covered.
除此之外，我发现在数据库端和应用程序端进行限制总是更好。一个枚举加上一个选择框，你就被覆盖了。
回答by Alex Aza
Yes, it is recommended to add check constraints. Check constraints are used to ensure the validity of data in a database and to provide data integrity. If they are used at the database level, applications that use the database will not be able to add invalid data or modify valid data so the data becomes invalid, even if the application itself accepts invalid data.
是的，建议添加检查约束。检查约束用于确保数据库中数据的有效性并提供数据完整性。如果在数据库级别使用它们，则使用该数据库的应用程序将无法添加无效数据或修改有效数据，从而使数据变为无效，即使应用程序本身接受无效数据。
In SQLite:
在 SQLite 中：
create table MyTable
(
name string check(name = "car" or name = "bike" or name = "van")
);
In MySQL:
在 MySQL 中：
create table MyTable
(
name ENUM('car', 'bike', 'van')
);
回答by Bob Probst
You would use a check constraint. In SQL Server it works like this
您将使用检查约束。在 SQL Server 中它是这样工作的
ALTER TABLE Vehicles
ADD CONSTRAINT chkVehicleType CHECK (VehicleType in ('car','bike','van'));
I'm not sure if this is ANSI standard but I'm certain that MySQL has a similar construct.
我不确定这是否是 ANSI 标准，但我确定 MySQL 具有类似的构造。
回答by Dilum Ranatunga
If you want to go with DB-side validation, you can use triggers. See thisfor SQLite, and this detailed how-tofor MySQL.
如果要进行 DB 端验证，可以使用触发器。请参阅此SQLite，以及此详细的 MySQL 操作方法。
So the question is really whether you should use Database validation or not. If you have multiple clients -- whether they are different programs, or multiple users (with possibly different versions of the program) -- then going the database route is definitely best. The database is (hopefully) centralized, so you can decouple some of the details of validation. In your particular case, you can verify that the value being inserted into the column is contained in a separate table that simply lists valid values.
所以问题是你是否应该使用数据库验证。如果您有多个客户端——无论它们是不同的程序，还是多个用户（可能有不同版本的程序）——那么走数据库路线绝对是最好的。数据库（希望）是集中式的，因此您可以分离验证的一些细节。在您的特定情况下，您可以验证插入列中的值是否包含在单独的表中，该表仅列出有效值。
On the other hand, if you have little experience with databases, plan to target several different databases, and don't have the time to develop expertise, perhaps simple application level validation is the most expedient choice.
另一方面，如果您对数据库的经验很少，计划针对几个不同的数据库，并且没有时间开发专业知识，那么简单的应用程序级别验证可能是最方便的选择。
