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

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

这是标准的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 AllChildrenASSELECT ChildID, 1 AS ENABLED  FROM EnabledChildrenUNIOnSELECt ChildID, 0 AS ENABLED  FROM ChildrenEXCEPTSELECt ChildID, 0 AS ENABLED  FROM EnabledChildren;