sql和access中的验证规则 [英] Validation rule in sql and access

问题描述

我正在尝试创建验证规则，但在 Access 中使用 SQL.我对此很陌生.我知道如何在常规 Access 设计视图中执行此操作，但在使用 create table 命令时不知道如何执行此操作.到目前为止，我有，

I am trying to create a validation rule but using SQL in Access. I am very new to this. I know how to do it in regular Access design view, but don't know how to do it when using the create table command. So far I have,

CREATE TABLE CUSTOMERS
(
    CustomerName TEXT (20),
    Gender TEXT (10),
    FavoriteFood TEXT (20)
);

想为男性/女性/未知的性别添加验证规则.

Would like to add a validation rule to gender for just male/female/notknown.

推荐答案

字段的验证规则是一个 DAO.TableDef 属性，无法使用 Access SQL 创建.

A field's Validation Rule is a DAO.TableDef property which can't be created using Access SQL.

如果您想将 Access SQL 用于类似的事情，请使用 CHECK CONSTRAINT.此语句会将 Gender 值限制为男性、女性或未知.

If you want to use Access SQL for something similar, use a CHECK CONSTRAINT. This statement will limit Gender values to male, female, or unknown.

CREATE TABLE CUSTOMERS
(
    CustomerName TEXT (20),
    Gender TEXT (10) NOT NULL,
    FavoriteFood TEXT (20),
    CONSTRAINT allowed_genders CHECK
        (
            Gender IN ('male','female','unknown')
        )
);

注意 CHECK CONSTRAINT 只能在从 ADO 执行时在 Access SQL 中使用.我使用 CurrentProject.Connection.Execute 执行了该语句，这是一种 ADO 方法.

Note a CHECK CONSTRAINT can only be used in Access SQL when executed from ADO. I executed that statement with CurrentProject.Connection.Execute, which is an ADO method.

这种方法的某些后果可能会让您无法接受......

Some consequences of this approach may make it unacceptable to you ...

1. 约束在表设计视图中不可见.
2. 当违反约束时，错误消息在您尝试保存记录之前不会出现.
3. 错误消息基于约束名称......在这种情况下这对用户不是很友好.
4. 由于限制，您无法从 Access UI 中删除表；您必须先执行 DROP CONSTRAINT allowed_genders 然后删除表，或者执行 DROP TABLE CUSTOMERS 这将同时丢弃表和约束.

1. The constraint is not visible in table Design View.
2. When the constraint is violated, the error message does not appear until you attempt to save the record.
3. And the error message is based on the constraint name ... which is not very user-friendly in this case.
4. Because of the constraint, you can not delete the table from the Access UI; you would have to first execute DROP CONSTRAINT allowed_genders and then delete the table, or execute DROP TABLE CUSTOMERS which will discard both the table and constraint at the same time.

您可能更喜欢创建一个更简单的表版本，然后使用 DAO 设置验证规则.

You might prefer to create a simpler version of the table and then set the Validation Rule with DAO.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.TableDefs("CUSTOMERS")
tdf.Fields("Gender").ValidationRule = "IN ('male','female','unknown')"
tdf.Fields("Gender").ValidationText = "Gender must be male, female, or unknown"

注意事项:

1. DAO 方法还允许您包含用户友好的验证文本消息.
2. 如果违反验证规则，Access 将在您尝试移至下一个字段时立即显示错误消息.

最后另一种方法是创建一个查找表，其中包含您允许的 Gender 值，使 CUSTOMERS.Gender 成为该查找字段的外键，并强制执行引用诚信.

Finally yet another approach would be to create a lookup table which contains your allowed Gender values, make CUSTOMERS.Gender a foreign key to that lookup field, and enforce referential integrity.

这 3 种方法中的任何一种都可能适合您的情况；选择看起来最合适的那个.:-)

Any of those 3 approaches might fit your situation; choose whichever seems to fit best. :-)

这篇关于sql和access中的验证规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！