ORA-00904标识符无效错误排查及外键约束配置求助

这个错误的核心原因是section表的外键引用了time_slot表的day和start_time字段，但section表本身没有定义这两个字段。外键约束要求当前表必须包含要引用的所有字段，才能和被引用表的主键列匹配。

修改后的section表创建语句如下，补充缺失的字段：

CREATE TABLE section (
    course_id VARCHAR(10),
    sec_id VARCHAR(10),
    semester VARCHAR(10),
    year NUMERIC(4, 0),
    building VARCHAR(10),
    room_number VARCHAR(10),
    time_slot_id VARCHAR(10),
    day NUMERIC(2, 0), -- 补充time_slot主键对应的day字段
    start_time DATE, -- 补充time_slot主键对应的start_time字段
    PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (course_id) REFERENCES course (course_id),
    FOREIGN KEY (time_slot_id, day, start_time) REFERENCES time_slot (time_slot_id, day, start_time),
    FOREIGN KEY (building, room_number) REFERENCES classroom (building, room_number)
);

要给department表添加foreign key(building) references classroom(building)，因为classroom的主键是复合主键(building, room_number)，必须先让classroom的building字段具备唯一性，你添加unique_building约束的思路是对的，但需要注意两点：

1. 先确认classroom表中已有的building数据没有重复值，否则添加唯一约束会直接失败
2. 完成唯一约束添加后，再给department表添加外键：

ALTER TABLE department
ADD CONSTRAINT fk_dept_building FOREIGN KEY (building) REFERENCES classroom (building);

之前添加唯一约束后创建section表报错，本质是section表的字段缺失问题（即第一个错误）未解决，修复section表的字段缺失后，重新创建section表即可正常引用classroom的复合主键——section的外键是同时匹配building和room_number，和classroom的building是否唯一没有冲突，唯一约束仅保证classroom中每个building值不重复，不影响复合主键的引用逻辑。

1. 先创建classroom表并添加唯一约束：

CREATE TABLE classroom (
    building VARCHAR (10),
    room_number VARCHAR (10),
    capacity INT,
    PRIMARY KEY( building, room_number));

ALTER TABLE classroom
ADD CONSTRAINT unique_building UNIQUE(building);

2. 创建department表并添加外键：

CREATE TABLE department (
    dept_name VARCHAR(20),
    building VARCHAR(10), 
    budget DOUBLE PRECISION,
    PRIMARY KEY (dept_name)
);

ALTER TABLE department
ADD CONSTRAINT fk_dept_building FOREIGN KEY (building) REFERENCES classroom (building);

3. 依次创建instructor、student、course、time_slot表
4. 创建修改后的section表（包含day和start_time字段）

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