对多个表的数据库约束

Question

假设我有以下表格：

CREATE TABLE [Weeks] 
(
    [Id] INT NOT NULL IDENTITY,
    CONSTRAINT [PK_Weeks] PRIMARY KEY ([Id])
);
CREATE TABLE [Days] 
(
    [Id] INT NOT NULL IDENTITY,
    [WeekId] INT NULL,
    CONSTRAINT [PK_Days] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Days_Weeks_WeekId] 
        FOREIGN KEY ([WeekId]) REFERENCES [Weeks] ([Id])
);
CREATE TABLE [ReplacedDayInWeek] 
(
    [Id] INT NOT NULL IDENTITY,
    [WeekId] INT NOT NULL,
    [DayId] INT NOT NULL,
    [ReplacedDayId] INT NOT NULL,
    CONSTRAINT [PK_ReplacedDayInWeek] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_ReplacedDayInWeek_Days_DayId] 
        FOREIGN KEY ([DayId]) REFERENCES [Days] ([Id]),
    CONSTRAINT [FK_ReplacedDayInWeek_Weeks_WeekId] 
        FOREIGN KEY ([WeekId]) REFERENCES [Weeks] ([Id]),
    CONSTRAINT [FK_ReplacedDayInWeek_Weeks_ReplacedWeekId] 
        FOREIGN KEY ([ReplacedWeekId]) REFERENCES [Weeks] ([Id])
);

"替换的日内周"表包含特定周中的某一天，该天被另一天替换。

如何创建一个 SQL 约束(或者可能是另一个基于 SQL 的解决方案(，以确保我只能在DayInWeek中插入行，DayId与WeekId是同一周的一部分？

我正在寻找对源数据库更改最少的解决方案。我更喜欢存储过程上方的附加表或表更改。

Answer 1

这种数据结构对我来说毫无意义。 您在两个表中具有天数和周之间的映射。

您实际上应该只将一周存储在一个表中，然后在另一个表中查找它。

也就是说，您可以使用两个表之间的天/周附加约束来执行所需的操作：

CREATE TABLE [Days] 
(
    [Id] INT NOT NULL IDENTITY,
    [WeekId] INT NULL,
    CONSTRAINT [PK_Days] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Days_Weeks_WeekId] 
        FOREIGN KEY ([WeekId]) REFERENCES [Weeks] ([Id]),
    CONSTRAINT UNQ_Days_WeekId_Id UNIQUE (WeekId, Id)
);
CREATE TABLE [DaysInWeek] 
(
    [Id] INT NOT NULL IDENTITY,
    [WeekId] INT NOT NULL,
    [DayId] INT NOT NULL,
    CONSTRAINT [PK_DaysInWeek] PRIMARY KEY([Id]),
    CONSTRAINT [FK_DaysInWeek_Days_WeekId_DayId] 
        FOREIGN KEY (WeekId, DayId) REFERENCES Days(WeekId, Id),
    CONSTRAINT [FK_DaysInWeek_Weeks_WeekId] 
        FOREIGN KEY ([WeekId]) REFERENCES [Weeks] ([Id])
);