外键引用列数与被引用列数不匹配问题及外键约束声明咨询

问题场景 #

尝试创建关联TimeSlot表的Section表，原DDL语句如下：

Section表原DDL #

create table Section 
(   
    course_id nvarchar(8) foreign key references Course(course_id) on delete cascade,
    sec_id nvarchar(8),
    semester nvarchar(6) check(semester in ('Spring','Fall','Summer','Winter')),
    year_ numeric(4,0) check(1700 < year_ and year_ < 2100),
    building nvarchar(15),
    room_number nvarchar(7), 
    time_slot_id nvarchar(4),
    primary key(course_id,sec_id,semester,year_),
    --Foreign key =>(Section to Classroom)
    constraint FK_Section_to_Classroom 
    foreign key(building,room_number) 
    references Classroom(building,room_number)
    on delete set null,
    --Foreign key =>(Section to TimeSlot)
    constraint FK_Section_to_TimeSlot 
    foreign key(time_slot_id) 
    references TimeSlot(time_slot_id,day_of_week,start_time)
    on delete set null
);

TimeSlot表原DDL #

create table TimeSlot
(
    time_slot_id nvarchar(4),
    day_of_week nvarchar(1) check(day_of_week in ('M', 'T', 'W', 'R', 'F', 'S', 'U')),
    start_time time,
    end_time time,
    primary key(time_slot_id,day_of_week,start_time)
);

执行时出现错误：Number of referencing columns in foreign key differs from number of referenced columns，需求是用Section表的单个time_slot_id属性关联TimeSlot表的复合主键。

错误原因 #

TimeSlot表的主键是复合主键（time_slot_id, day_of_week, start_time三个字段组合），外键约束要求引用的字段数量、顺序、数据类型必须和被引用的主键完全匹配。当前只使用time_slot_id单个字段关联，字段数量不匹配，因此报错。

解决方案 #

根据需求，有两种可行的调整方案：

方案1：调整TimeSlot表的主键结构 #

将time_slot_id设为TimeSlot的单一主键，同时为day_of_week和start_time添加唯一约束（确保同一时间段的时间槽不重复），这样就可以用Section的time_slot_id直接关联。

修改后的TimeSlot表DDL：

create table TimeSlot
(
    time_slot_id nvarchar(4) primary key, -- 设为单一主键
    day_of_week nvarchar(1) check(day_of_week in ('M', 'T', 'W', 'R', 'F', 'S', 'U')),
    start_time time,
    end_time time,
    -- 添加唯一约束，确保同一时间槽的日期和时段不重复
    constraint UQ_TimeSlot_Day_StartTime unique(day_of_week, start_time)
);

修改后的Section表外键约束：

--Foreign key =>(Section to TimeSlot)
constraint FK_Section_to_TimeSlot 
foreign key(time_slot_id) 
references TimeSlot(time_slot_id)
on delete set null

方案2：在Section表中补充关联字段 #

如果必须保留TimeSlot的复合主键，需要在Section表中添加day_of_week和start_time两个字段，然后用这三个字段组合作为外键关联TimeSlot的复合主键。

修改后的Section表DDL：

create table Section 
(   
    course_id nvarchar(8) foreign key references Course(course_id) on delete cascade,
    sec_id nvarchar(8),
    semester nvarchar(6) check(semester in ('Spring','Fall','Summer','Winter')),
    year_ numeric(4,0) check(1700 < year_ and year_ < 2100),
    building nvarchar(15),
    room_number nvarchar(7), 
    time_slot_id nvarchar(4),
    day_of_week nvarchar(1) check(day_of_week in ('M', 'T', 'W', 'R', 'F', 'S', 'U')), -- 新增字段
    start_time time, -- 新增字段
    primary key(course_id,sec_id,semester,year_),
    --Foreign key =>(Section to Classroom)
    constraint FK_Section_to_Classroom 
    foreign key(building,room_number) 
    references Classroom(building,room_number)
    on delete set null,
    --Foreign key =>(Section to TimeSlot)
    constraint FK_Section_to_TimeSlot 
    foreign key(time_slot_id, day_of_week, start_time) -- 三个字段组合关联
    references TimeSlot(time_slot_id, day_of_week, start_time)
    on delete set null
);

说明 #

如果业务逻辑中一个time_slot_id对应唯一的日期和时段，方案1更贴合需求；如果time_slot_id可以对应多个不同的日期和时段组合，方案2更符合数据一致性要求。

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