边缘约束Edge constraints

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

边缘约束可用于对 SQL ServerSQL Server 图形数据库中的边缘表强制执行数据完整性和特定语义。Edge constraints can be used to enforce data integrity and specific semantics on the edge tables in SQL ServerSQL Server graph database.

边缘约束Edge Constraints

在图形功能的第一个版本中,边缘表没有对边缘的终结点强制执行任何操作。In the first release of graph features, edge tables did not enforce anything for the endpoints of the edge. 也就是说,无论何种类型,图形数据库中的边缘都可以将任何节点连接到任何其他节点。That is, an edge in a graph database could connect any node to any other node, regardless of the type.

此版本引入了边缘约束,使用户能够向其边缘表添加约束,从而强制执行特定语义,同时保持数据完整性。This release introduces edge constraints, which enable users to add constraints to their edge tables, thereby enforcing specific semantics and also maintaining data integrity. 将新的边缘添加到具有边缘约束的边缘表时,数据库引擎Database Engine 强制使边缘尝试连接的节点存在于正确的节点表中。When a new edge is added to an edge table with edge constraints, the 数据库引擎Database Engine enforces that the nodes which the edge is trying to connect, exist in the proper node tables. 如果节点仍被边缘引用,则还要确保不会删除该节点。It is also ensured that a node cannot be dropped, if it is still referenced by an edge.

边缘约束子句Edge Constraint Clauses

每个边缘约束都由一个或多个边缘约束子句组成。Each edge constraint consists of one or more edge constraint clause(s). 边缘约束子句是给定边缘可以连接的 FROM 和 TO 节点对。An edge constraint clause is the pair of FROM and TO nodes that the given edge could connect.

假设图形中有 ProductCustomer 节点,并使用 Bought 边缘连接这些节点。Consider that you have Product and Customer nodes in your graph and you use Bought edge to connect these nodes. 边缘约束子句指定 FROM 和 TO 节点对以及边缘的方向。The edge constraint clause specifies the FROM and TO node pair and the direction of the edge. 在这种情况下,边缘约束子句将为 Customer TO ProductIn this case the edge constraint clause will be Customer TO Product. 也就是说,将允许插入范围从 CustomerProductBoughtThat is, inserting a Bought that goes from a Customer to Product will be allowed. 尝试插入范围从 ProductCustomer 的边缘失败。Attempts to insert an edge that goes from Product to Customer fail.

  • 边缘约束子句包含对其强制执行边缘约束的 FROM 和 TO 节点对表。An edge constraint clause contains a pair of FROM and TO node tables that the edge constraint is enforced on.
  • 用户可以为每个边缘约束指定多个边缘约束子句,这些子句将应用为析取。Users may specify multiple edge constraint clauses per edge constraint, which will be applied as a disjunction.
  • 如果在单个边缘表上创建多个边缘约束,则边缘必须满足允许的所有约束。If multiple edge constraints are created on a single edge table, edges must satisfy ALL constraints to be allowed.

边缘约束的索引Indexes on edge constraints

创建边缘约束不会自动在边缘表中的 $from_id$to_id 列上创建相应的索引。Creating an edge constraint does not automatically create a corresponding index on $from_id and $to_id columns in the edge table. 如果拥有点查找查询或 OLTP 工作负载,建议在“$from_id$to_id对上手动创建索引。Manually creating an index on a $from_id, $to_id pair is recommended if you have point lookup queries or OLTP workload.

ON 删除边缘约束上的引用操作ON DELETE referential actions on edge constraints

使用边缘约束上的级联操作,用户可以定义当用户删除给定边缘连接到的节点时,数据库引擎所采取的操作。Cascading actions on an edge constraint let users define the actions that the database engine takes when a user deletes the node(s), which the given edge connects. 可以定义以下引用操作:The following referential actions can be defined:
不执行任何操作 NO ACTION
尝试删除具有连接边缘的节点时,数据库引擎将引发错误。The database engine raises an error when you try to delete a node that has connecting edge(s).

级联 CASCADE
从数据库中删除某个节点时,会删除连接边缘。When a node is delete from the database, connecting edge(s) are deleted.

使用边缘约束Working with edge constraints

可以使用 Transact-SQLTransact-SQL 定义 SQL ServerSQL Server 中的边缘约束。You can define a edge constraint in SQL ServerSQL Server by using Transact-SQLTransact-SQL. 边缘约束只能在图形边缘表上进行定义。An edge constraint can be defined on a graph edge table only. 若要创建、删除或修改边缘约束,必须对表拥有 ALTER 权限。To create, delete, or modify an edge constraint, you must have ALTER permission on the table.

创建边缘约束Create edge constraints

下面几个示例展示了如何对新表或现有表创建边缘约束The following examples show you how to create an edge constraints on new or existing tables

在新边缘表上创建边缘约束To create an edge constraint on a new edge table

下面的示例对“bought” 边缘表创建边缘约束。The following example creates an edge constraint on the bought edge table.

-- CREATE node and edge tables
CREATE TABLE Customer
   (
      ID INTEGER PRIMARY KEY
      ,CustomerName VARCHAR(100)
   )
AS NODE;
GO
CREATE TABLE Product
   (
      ID INTEGER PRIMARY KEY
      ,ProductName VARCHAR(100)
   )
AS NODE;
GO
CREATE TABLE bought
   (
      PurchaseCount INT
         ,CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product) ON DELETE NO ACTION
   )
   AS EDGE;

定义对新边缘表的引用操作Defining referential actions on a new edge table

下面的示例对“bought”边缘表创建边缘约束并定义删除级联引用操作 。The following example creates an edge constraint on the bought edge table and defines ON DELETE CASCADE referential action.

-- CREATE node and edge tables
CREATE TABLE Customer
   (
      ID INTEGER PRIMARY KEY
      ,CustomerName VARCHAR(100)
   )
AS NODE;
GO
CREATE TABLE Product
   (
      ID INTEGER PRIMARY KEY
      ,ProductName VARCHAR(100)
   )
AS NODE;
GO
CREATE TABLE bought
   (
      PurchaseCount INT
         ,CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product) ON DELETE CASCADE
   )
   AS EDGE;

将边缘约束添加到现有边缘表的具体步骤To add edge constraint to an existing edge table

下面的示例使用 ALTER TABLE 将边缘约束添加到“bought” 边缘表。The following example uses ALTER TABLE to add an edge constraint to the bought edge table.

-- CREATE node and edge tables
CREATE TABLE Customer
   (
      ID INTEGER PRIMARY KEY,
      , CustomerName VARCHAR(100)
   )
   AS NODE;
CREATE TABLE Product
   (
      ID INTEGER PRIMARY KEY
      , ProductName VARCHAR(100)
   )
   AS NODE;
GO
CREATE TABLE bought
   (
      PurchaseCount INT
   )
   AS EDGE;
GO
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Customer TO Product);

对现有边缘表新建边缘约束(其中包含附加边缘约束子句)Create a new edge constraint on existing edge table, with additional edge constraint clauses

下面的示例使用 ALTER TABLE 命令,将包含附加边缘约束子句的新边缘约束添加到“bought” 边缘表。The following example uses the ALTER TABLE command to add a new edge constraint with additional edge constraint clauses on the bought edge table.

-- CREATE node and edge tables
CREATE TABLE Customer
   (
      ID INTEGER PRIMARY KEY
      , CustomerName VARCHAR(100)
   )
   AS NODE;
GO
CREATE TABLE Supplier
   (
      ID INTEGER PRIMARY KEY
      , SupplierName VARCHAR(100)
   )
   AS NODE;
GO
CREATE TABLE Product
   (
      ID INTEGER PRIMARY KEY
      , ProductName VARCHAR(100)
   )
   AS NODE;
GO
CREATE TABLE bought
   (
      PurchaseCount INT
      , CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
   )
   AS EDGE;
-- Drop the existing edge constraint first and then create a new one.
ALTER TABLE bought DROP CONSTRAINT EC_BOUGHT;
GO
-- User ALTER TABLE to create a new edge constraint.
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Customer TO Product, Supplier TO Product);

在上面的示例中,EC_BOUGHT1 约束中有两个边缘约束子句,一个用于将“Customer” 连接到“Product” ,另一个用于将“Supplier” 连接到“Product” 。In the preceding example, there are two edge constraint clauses in EC_BOUGHT1 constraint, one that connects Customer to Product and other connects Supplier to Product. 这两个子句都可应用于析取。Both these clauses are applied in disjunction. 即,给定的边缘必须满足这两个子句之一,才能在边缘表中使用。That is, a given edge has to satisfy either of these two clauses to be allowed in the edge table.

对现有边缘表新建边缘约束(其中包含新边缘约束子句)Creating a new edge constraint on existing edge table, with new edge constraint clause

下面的示例使用 ALTER TABLE 命令,将包含新边缘约束子句的新边缘约束添加到“bought” 边缘表。The following example uses the ALTER TABLE command to add a new edge constraint with a new edge constraint clause on the bought edge table.

-- CREATE node and edge tables
CREATE TABLE Customer
  (
     ID INTEGER PRIMARY KEY
     , CustomerName VARCHAR(100)
  )
  AS NODE;
GO
CREATE TABLE Supplier
  (
     ID INTEGER PRIMARY KEY
     , SupplierName VARCHAR(100)
  )
  AS NODE;
GO
CREATE TABLE Product
  (
     ID INTEGER PRIMARY KEY
     , ProductName VARCHAR(100)
  )
  AS NODE;
GO
CREATE TABLE bought
  (
     PurchaseCount INT,
        CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
  )
  AS EDGE;
GO
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT1 CONNECTION (Supplier TO Product);

上面的示例对“bought” 边缘表单独创建了两个边缘约束:EC_BOUGHT 和 EC_BOUGHT1 。In the preceding example, we created two separate edge constraints on the bought edge table, EC_BOUGHT and EC_BOUGHT1. 这两个边缘约束都具有不同的边缘约束子句。Both these edge constraints have different edge constraint clauses. 如果一个边缘表在其上具有多个边缘约束,则给定的边缘表必须满足所有 边缘约束,才能在边缘表中使用它。If an edge table has more than one edge constraint on it, a given edge has to satisfy ALL edge constraints to be allowed in the edge table. 因为此处没有能够同时满足 EC_BOUGHT 和 EC_BOUGHT1 的任何边缘,所以购买 边缘表必须保持为空。Since no edge will be able to satisfy both EC_BOUGHT and EC_BOUGHT1 here, the bought edge table must remain empty.

为了在此边缘表中成功实现插入操作,必须删除其中的一个边缘约束,或必须将这两个边缘约束全部删除,且应创建一个新的边缘约束,该约束中包含这两个边缘约束子句。For inserts to succeed in this edge table, either one of the edge constraint must be dropped, or both of them must be dropped and a new edge constraint should be created which has both edge constraint clauses in it.

-- Drop the existing edge constraint first and then create a new one.
ALTER TABLE bought DROP CONSTRAINT EC_BOUGHT;
GO
ALTER TABLE bought DROP CONSTRAINT EC_BOUGHT1;
GO
ALTER TABLE bought ADD CONSTRAINT EC_BOUGHT_NEW CONNECTION (Customer TO Product, Supplier TO Product);
GO

为了能够存在于“bought” 边缘中,给定边缘必须满足 EC_BOUGHT_NEW 约束中的两个边缘约束子句之一。For a given edge to be allowed in the bought edge, it has to satisfy either of the edge constraint clauses in EC_BOUGHT_NEW constraint. 因此,将允许使用尝试将有效的客户 连接到产品 或将供应商 连接到产品 节点的任何边缘。Hence any edge that is trying to connect valid Customer to Product or Supplier to Product nodes, will be allowed.

删除边缘约束Delete edge constraints

下面的示例先标识边缘约束名称,再删除边缘约束。The following example first identifies the name of the edge constraint and then deletes the constraint.

-- CREATE node and edge tables
CREATE TABLE Customer
   (
      ID INTEGER PRIMARY KEY
      , CustomerName VARCHAR(100)
   )
   AS NODE;
GO
CREATE TABLE Product
   (
      ID INTEGER PRIMARY KEY
      , ProductName VARCHAR(100)
   ) AS NODE;
GO
CREATE TABLE bought
   (
      PurchaseCount INT
      , CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product)
    )
    AS EDGE;
GO
-- Return the name of edge constraint.
SELECT name  
   FROM sys.edge_constraints  
   WHERE type = 'EC' AND parent_object_id = OBJECT_ID('bought');  
GO  
-- Delete the primary key constraint.  
ALTER TABLE bought
DROP CONSTRAINT EC_BOUGHT;

修改边缘约束Modify edge constraints

若要使用 Transact-SQL 修改边缘约束,必须首先删除现有的边缘约束,然后用新定义重新创建。To modify an edge constraint using Transact-SQL, you must first delete the existing edge constraint and then re-create it with the new definition.

查看边缘约束View edge constraints

目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

此实例返回 tempdb 数据库中的边缘表 bought 的所有边缘约束及其属性。The example returns all edge constraints and their properties for the edge table bought in the tempdb database.

-- CREATE node and edge tables
CREATE TABLE Customer
   (
      ID INTEGER PRIMARY KEY
      , CustomerName VARCHAR(100)
   )
   AS NODE;
GO
CREATE TABLE Supplier
   (
      ID INTEGER PRIMARY KEY
      , SupplierName VARCHAR(100)
   )
   AS NODE;
   GO
CREATE TABLE Product
   (
      ID INTEGER PRIMARY KEY
      , ProductName VARCHAR(100)
   )
   AS NODE;

-- CREATE edge table with edge constraints.
CREATE TABLE bought
   (
      PurchaseCount INT
      , CONSTRAINT EC_BOUGHT CONNECTION (Customer TO Product, Supplier TO Product)
   )
   AS EDGE;

-- Query sys.edge_constraints and sys.edge_constraint_clauses to view
-- edge constraint properties.
SELECT
   EC.name AS edge_constraint_name
   , OBJECT_NAME(EC.parent_object_id) AS edge_table_name
   , OBJECT_NAME(ECC.from_object_id) AS from_node_table_name
   , OBJECT_NAME(ECC.to_object_id) AS to_node_table_name
   , is_disabled
   , is_not_trusted
FROM sys.edge_constraints EC
   INNER JOIN sys.edge_constraint_clauses ECC
   ON EC.object_id = ECC.object_id
WHERE EC.parent_object_id = object_id('bought');

CREATE TABLE(SQL 图形)CREATE TABLE (SQL Graph)
ALTER TABLE table_constraintALTER TABLE table_constraint

若要了解 SQL Server 中的图形技术,请参阅使用 SQL Server 和 Azure SQL 数据库进行图形处理For information about graph technology in SQL Server, see Graph processing with SQL Server and Azure SQL Database.