SQL-建立表格-1到3

PMax 发表于 Dev

最大功率

我想要这个设置：

TABLE [group] :
[group_id] INT IDENTITY(1,1) --as pkey,
[member_one] INT NOT NULL --as fkey [member].[member_id],
[member_two] INT NULL --as fkey [member].[member_id],
[member_three] INT NULL --as fkey [member].[member_id],
...

TABLE [member] :
[member_id] INT IDENTITY(1,1) --as pkey,
[group_id] INT NOT NULL --as fkey [group].[group_id],
...

用词：我和成员有小组。每个成员只有一个小组。

编辑1：每个组至少有一个成员，member_one是领导者，不能更改。每个小组可能有无限数量的成员，但是只有一个到三个成员（包括领导者）可以被我们称为“总参谋部”。---

如果我想同时创建一个小组和一个领导者，该怎么办？没有领导者，我就无法创建组；没有领导者，我就无法创建领导者。我不想在两个表中都删除“ NOT NULL”。

我不知道如何使用存储的proc，因此，如果它使用它（我不说应该使用它），那就容易吧！

提前致谢！

博格丹·萨林（Bogdan Sahlean）

我将使用以下数据逻辑模型：

Group(GroupID - PK, ...)
GroupMemberType(GroupMemberTypeID - PK, Name, IsHeadStaff)
    Example: 
        1 - Leader - 1
        2 - Power member - 1
        3 - Standard member - 2
    Reason: this way we can add easily [new] types
Member(MemberID - PK, ..., GroupID - FK, GroupMemberTypeID - FK)

如果一个组只能有一个领导者，那么我将创建一个唯一的过滤索引（假设GroupMemberTypeID = 1-Leader

CREATE UNIQUE NONCLUSTERED INDEX INF_Member_GroupID_GroupMemberTypeID1
ON dbo.Member (GroupID)
WHERE GroupMemberTypeID = 1 -- Leader

要检查是否将当前插入和/或更新的成员分配给一个组，并且对于当前组，最多可以有3名总参谋部，我将使用AFTER INSERT/UPDATE触发器：

CREATE TRIGGER trgIU_Member_CheckMax3HeadStaff
ON dbo.Member
AFTER INSERT, UPDATE
AS
BEGIN

IF EXISTS(
    SELECT *
    FROM dbo.Member m JOIN dbo.GroupMemberType gmt ON m.GroupMemberTypeID = gmt.GroupMemberTypeID
    WHERE m.GroupID IN (SELECT i.GroupID FROM inserted i)
    AND gmt.IsHeadStaff = 1
    GROUP BY m.GroupID
    HAVING COUNT(*) > 3
)
BEGIN
    ROLLBACK
    RAISERROR('One group can have maximum three head staff members,', 16, 1)
END

END