"Number of referencing columns in foreign key differs from number of referenced columns" inline error

Question

I'm trying to create a Section table that refers to Time Slot table.details of attributes is shown in the picture. how to declare foreign key constraint? Red line is where I get this error

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

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

Section table and Time Slot table DDL in SQL Server

The Section table foreign key is one single attribute that I want to refer to primary key of Time slot table in which is a set of attributes. Is there any other way to declare foreign key constraint?

Answer 1

You asked how to declare a foreign key from Section to TimeSlot.

That would be explicity stating that each row in Section refers to one row in TimeSlot. Your comment confirms that is not what you want.

What you're describing requires a third table to act as a link / association.

CREATE TABLE DimTimeSlot (
  id             INT IDENTITY(1,1),
  time_slot_id   NVARCHAR(4)  PRIMARY KEY
);

create table Section 
(   
    ...
    time_slot_id nvarchar(4),
    ...
    --Foreign key =>(Section to DimTimeSlot)
    constraint FK_Section_to_DimTimeSlot 
      foreign key(time_slot_id) 
      references DimTimeSlot(time_slot_id)
        on delete set null
);

create table TimeSlot
(
    time_slot_id nvarchar(4),
    ...
    --Foreign key =>(TimeSlot to DimTimeSlot)
    constraint FK_TimeSlot_to_DimTimeSlot 
      foreign key(time_slot_id) 
      references DimTimeSlot(time_slot_id)
        on delete cascade
);

This way...

If a row in DimTimeSlot is deleted the rows in Section get set to NULL and the rows in TimeSlot get deleted.

A row in Section references one row in DimTimeSlot, but that references N rows in TimeSlot.