多表数据库约束：确保ReplacedDayInWeek的DayId与WeekId同属一周

Let's tackle this problem directly—you need to ensure that whenever a row is added to ReplacedDayInWeek, the DayId you're referencing actually belongs to the WeekId specified in that same row. Here are two lightweight, table-structure-focused solutions that require minimal changes to your existing database:

Option 1: Add a Check Constraint (Simplest Approach) #

This is the quickest fix, as it only adds a single constraint without modifying the table's column structure. The constraint uses a subquery to validate that the DayId's associated WeekId in the Days table matches the WeekId in the ReplacedDayInWeek row:

ALTER TABLE [ReplacedDayInWeek]
ADD CONSTRAINT [CK_ReplacedDayInWeek_DayId_Matches_WeekId]
CHECK (
    EXISTS (
        SELECT 1
        FROM [Days] d
        WHERE d.[Id] = [ReplacedDayInWeek].[DayId]
        AND d.[WeekId] = [ReplacedDayInWeek].[WeekId]
    )
);

Every insert or update on ReplacedDayInWeek will trigger this check. If the DayId doesn't belong to the specified WeekId, the operation will fail immediately.

Option 2: Persisted Computed Column + Check Constraint (Better for Large Tables) #

If you're working with large datasets and want to avoid repeated subquery execution during validation, this approach adds a persisted computed column that stores the WeekId linked to the DayId, then ensures it matches the table's WeekId:

1. First, add the computed column (it will automatically pull the WeekId from Days for each DayId):

ALTER TABLE [ReplacedDayInWeek]
ADD [DayAssociatedWeekId] AS (
    SELECT d.[WeekId] FROM [Days] d WHERE d.[Id] = [ReplacedDayInWeek].[DayId]
) PERSISTED;

2. Then add a check constraint to enforce equality between the computed column and the table's WeekId:

ALTER TABLE [ReplacedDayInWeek]
ADD CONSTRAINT [CK_ReplacedDayInWeek_WeekIds_Match]
CHECK ([DayAssociatedWeekId] = [WeekId]);

The PERSISTED keyword means the value is stored physically, so validation is faster than running a subquery every time.

Quick Heads-Up: #

• Before adding either constraint, make sure there are no existing invalid rows in ReplacedDayInWeek (where a DayId's WeekId doesn't match the row's WeekId). The ALTER TABLE command will fail if invalid data exists.
• Note: Your original ReplacedDayInWeek schema has a foreign key FK_ReplacedDayInWeek_Weeks_ReplacedWeekId that references Weeks(Id), but the column ReplacedWeekId isn't defined in the table. That looks like a typo—you probably meant to reference Days(Id) for ReplacedDayId instead. It's worth fixing that separately to maintain referential integrity.

内容的提问来源于stack exchange，提问作者Nick Muller