如何限制SQL表中允许的记录数？ [英] How to constrain the number of records allowed in an SQL table?

问题描述

说我有两个表，Parent和Child。 Parent有一个MaxChildren（int）字段，Child有一个Enabled（bit）字段和一个ParentID（int）字段链接回父记录。

Say I have two tables, Parent and Child. Parent has a MaxChildren (int) field and Child has an Enabled (bit) field and a ParentID (int) field linking back to the parent record.

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

I'd like to have a constraint such that there can't be more than MaxChildren records for each parent where Enabled = 1. This would mean that any attempt to insert or update any record in the Child table will fail if it goes over the applicable MaxChildren value, or any attempt to lower MaxChildren to below the current number of applicable Child records will fail.

我使用MS SQL Server，但我希望有一个标准的SQL

I'm using MS SQL Server, but I'm hoping there's a standard SQL way.

推荐答案

这是标准SQL-92入门级语法，即使用'vanilla'语法如外键和行级 CHECK 在SQL产品中广泛实现的约束（尽管显着不是mySQL）：

This is Standard SQL-92 entry level syntax i.e. uses 'vanilla' syntax such as foreign keys and row level CHECK constraints that are widely implemented in SQL products (though notably not 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列。你可能需要三个表来建模：所有子类型的子类型表的启用的超类型表。然后，您可以创建一个 VIEW 到 UNION 两个子类型，其中包含启用的列。

I suggest you avoid using bit flag columns. Rather, you could have a second table without the restriction on MaxChildren then imply the Enabled column based on which table a row appears in. You'd probably want three tables to model this: a supertype table for all children with a subtype tables for Enabled. You could then create a VIEW to UNION the two subtypes with an implied Enabled column e.g.

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;

这篇关于如何限制SQL表中允许的记录数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！