ORA-02270错误排查：SQL新手创建关联表遇主键不匹配问题

问题描述 #

刚接触SQL，尝试创建以下两张表时持续报错。移除Date_Reserved字段后能成功创建，但即使给该字段设置了复合主键，仍无法正常执行。

原SQL代码 #

create table reservation 
 ( Booking_Number    varchar(8)    not null,
   Room_Number       number(1,50)  not null,
   Date_Reserved     date          not null,
   primary key (Booking_Number, Room_Number, Date_Reserved),
   foreign key (Booking_Number) references booking(Booking_Number),
   foreign key (Room_Number)    references room(Room_Number)
);
 
create table additional_extra
  ( Booking_Number    varchar(8)   not null,
    Room_Number       number(1,50) not null,
    Extra_ID          varchar(8)   not null,
    Date_Reserved     date          not null,
    primary key (Booking_Number, Room_Number, Extra_ID),
    foreign key (Booking_Number) references booking(Booking_Number),
    foreign key (Date_Reserved)  references reservation(Date_Reserved),
    foreign key (Room_Number)    references room(Room_Number),
    foreign key (Extra_ID)       references extra(Extra_ID)
);

报错信息 #

Error report -
ORA-02270: 此列列表没有匹配的唯一键或主键
02270. 00000 - "no matching unique or primary key for this column-list"
*原因: CREATE/ALTER TABLE语句中的REFERENCES子句给出的列列表，在被引用表中没有对应的唯一键或主键约束。
*操作: 使用ALL_CONS_COLUMNS数据字典视图查找正确的列名

错误原因 #

additional_extra表中单独用Date_Reserved作为外键关联reservation表的Date_Reserved字段是错误的：

• reservation表的主键是复合主键(Booking_Number, Room_Number, Date_Reserved)，这三个字段共同构成表的唯一标识，单独的Date_Reserved字段并不具备唯一性。
• 外键约束要求引用的必须是被引用表的主键或唯一键，单独的Date_Reserved不满足这个条件。

另外注意：number(1,50)的定义不符合常规逻辑，number类型的格式是number(精度, 小数位数)，这里精度1、小数位数50是不合理的，应该改成number(5)（表示最多5位整数）或number(3,0)这类符合业务需求的定义。

修正后的SQL代码 #

create table reservation 
 ( Booking_Number    varchar(8)    not null,
   Room_Number       number(5)     not null, -- 修正number类型定义
   Date_Reserved     date          not null,
   primary key (Booking_Number, Room_Number, Date_Reserved),
   foreign key (Booking_Number) references booking(Booking_Number),
   foreign key (Room_Number)    references room(Room_Number)
);
 
create table additional_extra
  ( Booking_Number    varchar(8)   not null,
    Room_Number       number(5)     not null, -- 同步修正number类型定义
    Extra_ID          varchar(8)   not null,
    Date_Reserved     date          not null,
    primary key (Booking_Number, Room_Number, Extra_ID),
    foreign key (Booking_Number) references booking(Booking_Number),
    -- 修改为组合外键，关联reservation的完整主键
    foreign key (Booking_Number, Room_Number, Date_Reserved) references reservation(Booking_Number, Room_Number, Date_Reserved),
    foreign key (Room_Number)    references room(Room_Number),
    foreign key (Extra_ID)       references extra(Extra_ID)
);

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