这是标准的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列为两个子类型创建aVIEW，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;
602392714
清零编程群