如何限制SQL表中允许的记录数？

小编典典

如何限制SQL表中允许的记录数？

sql

说我有两个表，父母和孩子。父级具有一个MaxChildren（int）字段，而子级具有一个Enabled（bit）字段和一个ParentID（int）字段，这些字段链接回父级记录。

我希望有一个约束，使得每个父项的Enabled =
1不能超过MaxChildren记录。这意味着，如果插入或更新Child表中的任何记录的任何尝试都将失败，则它将失败。适用的MaxChildren值，或任何将MaxChildren降低到适用的Child记录的当前数量以下的尝试都将失败。

我正在使用MS SQL Server，但我希望有一种标准的SQL方法。

阅读 198

2021-04-22

共1个答案

小编典典

这是标准的SQL-92入门级语法，即使用“香草”语法（例如外键和行级CHECK约束），这些语法已在SQL产品中广泛实现（尽管不是mySQL）：

CREATE TABLE Parent
(
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL
    CHECK (MaxChildren > 0), 
 UNIQUE (ParentID),
 UNIQUE (ParentID, MaxChildren)
);

CREATE TABLE Child
(
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL, 
 FOREIGN KEY (ParentID, MaxChildren)
    REFERENCES Parent (ParentID, MaxChildren)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 OccurrenceNumber INTEGER NOT NULL, 
 CHECK (OccurrenceNumber BETWEEN 1 AND MaxChildren), 
 UNIQUE (ParentID, OccurrenceNumber)
);

我建议您避免使用位标志列。相反，您可以有第二个表而不受限制，MaxChildren然后根据行出现在哪个表上来暗示“已启用”列。您可能希望使用三个表对此进行建模：一个用于所有子级的超类型表，以及用于“已启用”的子类型表。然后，您可以使用隐含的Enabled列为两个子类型创建a
VIEW，UNION例如

CREATE TABLE Parents
(
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL
    CHECK (MaxChildren > 0), 
 UNIQUE (ParentID),
 UNIQUE (ParentID, MaxChildren)
);

CREATE TABLE Children
(
 ChildID INTEGER NOT NULL, 
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL, 
 FOREIGN KEY (ParentID, MaxChildren)
    REFERENCES Parents (ParentID, MaxChildren)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 UNIQUE (ChildID), 
 UNIQUE (ChildID, MaxChildren),  
);

CREATE TABLE EnabledChildren
(
 ChildID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL, 
 FOREIGN KEY (ChildID, MaxChildren)
    REFERENCES Children (ChildID, MaxChildren)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 OccurrenceNumber INTEGER NOT NULL, 
 CHECK (OccurrenceNumber BETWEEN 1 AND MaxChildren), 
 UNIQUE (ChildID)
);

CREATE VIEW AllChildren
AS
SELECT ChildID, 1 AS ENABLED
  FROM EnabledChildren
UNION
SELECT ChildID, 0 AS ENABLED
  FROM Children
EXCEPT
SELECT ChildID, 0 AS ENABLED
  FROM EnabledChildren;

2021-04-22