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 ...
- 约束在表设计视图中不可见.
- 当违反约束时,错误消息在您尝试保存记录之前不会出现.
- 错误消息基于约束名称......在这种情况下这对用户不是很友好.
- 由于限制,您无法从 Access UI 中删除表;您必须先执行
DROP CONSTRAINT allowed_genders
然后删除表,或者执行DROP TABLE CUSTOMERS
这将同时丢弃表和约束.
- The constraint is not visible in table Design View.
- When the constraint is violated, the error message does not appear until you attempt to save the record.
- And the error message is based on the constraint name ... which is not very user-friendly in this case.
- 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 executeDROP 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"
注意事项:
- DAO 方法还允许您包含用户友好的验证文本消息.
- 如果违反验证规则,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屋!