Database constraint over multiple tables

Question

Say I have the following tables:

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])
);

The table ReplacedDayInWeek contains a day in a specific week that's replaced by another day.

How can I create a SQL constraint (or perhaps another SQL based solution) that makes sure I can only insert rows into DayInWeek with a DayId that's part of the same week as WeekId?

I'm looking for a solution with the least amount of changes to the source database. I'd prefer an additional table or table changes above stored procedures.

Answer 1

This data structure makes no sense to me. You have the mapping between days and weeks in two tables.

You should really only be storing the week in one table and looking it up in the other.

That said, you can do what you want using an additional constraint on day/week between the two tables:

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])
);