SQL新手用Live SQL遇ORA-02291外键约束错误求助

核心错误原因 #

你遇到的ORA-02291错误，根源在于外键关联逻辑错误，同时表结构的主键设计也存在不合理之处：

1. 外键关联不匹配：
   HOSPITAL_SPECIALTY表的外键FK_HOSPITAL_SPECIALTY_ID_PATIENT关联了PATIENT(HOSPITAL_ID)，但你插入HOSPITAL_SPECIALTY时的HOSPITAL_SPECIALTY_ID值是111、112...，这些值并不存在于PATIENT表的HOSPITAL_ID字段（PATIENT的HOSPITAL_ID只有11、12、13、14、15），数据库找不到对应的父键，因此触发约束错误。

2. 主键设计逻辑错误：

   • PATIENT表把HOSPITAL_ID设为主键，不符合业务逻辑：一个医院可以接收多个病人，HOSPITAL_ID不应该作为唯一主键，应该把PATIENT.ID设为主键。
   • HOSPITAL_SPECIALTY表把SPECIALTY_ID设为主键，导致插入多条相同SPECIALTY_ID=1的记录时，会触发主键重复错误。
   • SPECIALTY表的外键FK_SPECIALTY_TYPE_HOSPITAL_SPECIALTY关联HOSPITAL_SPECIALTY(SPECIALTY_ID)，但HOSPITAL_SPECIALTY的SPECIALTY_ID是主键且插入值全为1，后续插入SPECIALTY时SPECIALTY_TYPE是33、44...，同样找不到父键。

修正后的SQL代码 #

以下是修正了表结构和插入逻辑的完整代码，注释标注了修改点：

-- 按依赖顺序删除表，避免外键约束报错
DROP TABLE SPECIALTY;
DROP TABLE HOSPITAL_SPECIALTY;
DROP TABLE PATIENT;
DROP TABLE HOSPITAL;

CREATE TABLE HOSPITAL( 
    ID INT NOT NULL PRIMARY KEY, 
    NAME VARCHAR(255) NOT NULL, 
    ADDRESS VARCHAR(255) NOT NULL, 
    PHONE VARCHAR2(20) NOT NULL -- 修改为VARCHAR2，避免电话号码超出INT范围
);

CREATE TABLE PATIENT( 
    ID INT NOT NULL PRIMARY KEY, -- 修正：将病人自身ID设为主键
    NAME VARCHAR(255) NOT NULL, 
    ADDRESS VARCHAR(255) NOT NULL, 
    PHONE VARCHAR2(20) NOT NULL, -- 修改为VARCHAR2适配长号码
    EMERGENCY_CONTACT VARCHAR(255) NOT NULL, 
    HOSPITAL_ID INT NOT NULL,
    CONSTRAINT FK_HOSPITAL_ID_HOSPITAL FOREIGN KEY (HOSPITAL_ID) REFERENCES HOSPITAL(ID)
);

CREATE TABLE SPECIALTY( -- 调整创建顺序：先创建专科表，因为医院-专科关联表依赖它
    SPECIALTY_ID INT NOT NULL PRIMARY KEY,
    SPECIALTY_TYPE_NAME VARCHAR(255) NOT NULL -- 删除不合理的外键，直接存储专科名称
);

CREATE TABLE HOSPITAL_SPECIALTY( 
    HOSPITAL_SPECIALTY_ID INT NOT NULL PRIMARY KEY, -- 新增独立主键，避免重复冲突
    HOSPITAL_ID INT NOT NULL, 
    SPECIALTY_ID INT NOT NULL,
    CONSTRAINT FK_HOSPITAL_ID_HOSPITAL_SPECIALTY FOREIGN KEY (HOSPITAL_ID) REFERENCES HOSPITAL(ID),
    CONSTRAINT FK_SPECIALTY_ID_HOSPITAL_SPECIALTY FOREIGN KEY (SPECIALTY_ID) REFERENCES SPECIALTY(SPECIALTY_ID),
    CONSTRAINT UK_HOSPITAL_SPECIALTY UNIQUE (HOSPITAL_ID, SPECIALTY_ID) -- 新增唯一约束：同一医院的同一专科只关联一次
);

-- 插入医院数据
INSERT INTO HOSPITAL VALUES (11, 'Super Good Hospital', '999 Speedy Recover, ATL, GA, 99999', '1232223333');
INSERT INTO HOSPITAL VALUES (12, 'Piedmont Hospital', '111 Get Better, ATL, GA, 99990', '1232223334');
INSERT INTO HOSPITAL VALUES (13, 'Northside Hospital', '222 Bum Knee, ATL, GA, 99991', '1232223335');
INSERT INTO HOSPITAL VALUES (14, 'Emory Hospital', '333 Broken Hip, ATL, GA, 99992', '1232223336');
INSERT INTO HOSPITAL VALUES (15, 'Northside Hospital', '111 Bum Knee, ATL, GA, 99991', '1232223334');

-- 插入病人数据
INSERT INTO PATIENT VALUES (37442, 'Billy Jeans', '1111 Somewhere Here, ATL, GA, 11111', '4048888888', 'Jimmy John', 15);
INSERT INTO PATIENT VALUES (37443, 'Allie Grater', '2222 Somewhere There, ATL, GA, 11112', '4048888889', 'Jimmy John', 11);
INSERT INTO PATIENT VALUES (37444, 'Peg Legge', '3333 Somewhere Where, ATL, GA, 11113', '4048888880', 'Jimmy John', 13);
INSERT INTO PATIENT VALUES (37445, 'Olive Yew', '4444 Somewhere Overthere, ATL, GA, 11114', '4048888881', 'Jimmy John', 12);
INSERT INTO PATIENT VALUES (37446, 'Anne T Dote', '5555 Somewhere Overhere, ATL, GA, 11115', '4048888882', 'Jimmy John', 14);

-- 先插入专科数据（医院-专科关联表依赖此表）
INSERT INTO SPECIALTY VALUES (1, 'Brain');
INSERT INTO SPECIALTY VALUES (2, 'General');
INSERT INTO SPECIALTY VALUES (3, 'Surgery');
INSERT INTO SPECIALTY VALUES (4, 'Covid');
INSERT INTO SPECIALTY VALUES (5, 'Bodily Injured');

-- 插入医院-专科关联数据
INSERT INTO HOSPITAL_SPECIALTY VALUES (111, 11, 1);
INSERT INTO HOSPITAL_SPECIALTY VALUES (112, 11, 2);
INSERT INTO HOSPITAL_SPECIALTY VALUES (113, 12, 3);
INSERT INTO HOSPITAL_SPECIALTY VALUES (114, 13, 4);
INSERT INTO HOSPITAL_SPECIALTY VALUES (115, 14, 5);

-- 查询验证
SELECT * FROM HOSPITAL;
SELECT * FROM PATIENT;
SELECT * FROM SPECIALTY;
SELECT * FROM HOSPITAL_SPECIALTY;

关键修改说明 #

• 主键调整：将PATIENT的主键改为自身ID，符合“一个病人对应唯一ID”的业务逻辑；给HOSPITAL_SPECIALTY新增独立主键，避免用SPECIALTY_ID导致的重复冲突。
• 外键逻辑修正：删除不合理的HOSPITAL_SPECIALTY关联PATIENT的外键，改为分别关联HOSPITAL和SPECIALTY的主键，符合“医院和专科多对多关联”的业务逻辑。
• 表创建顺序：先创建被依赖的表（SPECIALTY），再创建依赖它的HOSPITAL_SPECIALTY，避免外键依赖报错。
• 字段类型修正：将电话号码字段改为VARCHAR2，避免INT类型无法存储超长号码的问题。
• 插入顺序调整：先插入SPECIALTY数据，再插入HOSPITAL_SPECIALTY，确保外键关联的父键已存在。

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