如何使用SQL查询在Access中的字段上设置验证规则? [英] How to set a validation rule on a field in Access using SQL Query?

问题描述

我正在使用MS Access 2016(Office 365)，当前遇到问题.下面是一个演示此问题的示例.

I am using MS Access 2016 (Office 365) and I am currently facing an issue. Below is an example demonstrating this issue.

在这里，我创建了一个名为 NodeFamilyLink 的表，该表包含两个字段:NodeID和FamilyID，如下所示.

Here I created a Table named as NodeFamilyLink, which consists of two fields: NodeID and FamilyID as shown below.

现在，从另一个表NodeData中获取NodeID，该表包含3个字段:NodeID，NodeName和ParentID.在此表中，最初定义了所有节点，如下图所示.

Now the NodeID's are obtained from another table NodeData, which consists of 3 fields: NodeID, NodeName and ParentID. In this table, all the nodes are defined initially as shown in the below image.

现在，我要在NodeFamilyLink表的NodeID列中输入数字时，如果输入的NodeID值未包含在NodeData表的ParentID列中，则该NodeID值将被视为有效值和其他值将被视为无效值.因此，我想使用查询将验证规则应用于NodeFamilyLink表的NodeID字段.但是问题是我找不到在ACCESS中使用查询设置验证规则的选项(如下图所示).

Now, what I want is while entering a number inside the NodeID column of the NodeFamilyLink table, if I enter a NodeID value which is not included in the ParentID column of the NodeData table, then that NodeID value will be considered as a valid one and rest other values will considered as invalid ones. So, I want to apply a validation rule on the NodeID field of the NodeFamilyLink table using a query. But the problem is that I didn't find an option to set the validation rule using query in ACCESS (as shown in the below image).

尽管我可以通过使用OLE-DB API(在VB.NET中)进行编程来轻松实现相同的目标，但是我想知道是否有任何方法可以帮助我完成任务而无需编写任何代码来它.请帮忙.

Although I can easily achieve the same by programming with the OLE-DB API (in VB.NET), but I want to know if there is any method with the help of which I can accomplish my task without writing any code for it. Please help.

推荐答案

您可能正在寻找CHECK约束:

ALTER TABLE NodeFamilyLink
ADD CONSTRAINT myConstraintName
CHECK (
   NOT EXISTS( SELECT 1 FROM NodeFamilyLink INNER JOIN NodeData ON NodeData.ParentID = NodeFamilyLink.NodeID)
)

请注意，这些只能通过ADO(使用CurrentProject.Connection.Execute)或数据库使用ANSI-92兼容语法来添加.

Note that these can only be added through ADO (by using CurrentProject.Connection.Execute), or when the database is using ANSI-92 compatible syntax.

这篇关于如何使用SQL查询在Access中的字段上设置验证规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！