新增表记录触发外键报错：关联表需存在对应记录问题咨询

尝试向数据库表中新增条目时遇到如下报错：

无法新增或修改记录，因为关联表中需要存在对应的关联记录（原文：You cannot add or change a record because a related record is required in table）

CREATE TABLE ART_OBJECT
(
    Id_no NUMBER PRIMARY KEY,
    Artist VARCHAR(20),
    Year INT UNIQUE,
    Title VARCHAR(20),
    Description VARCHAR(20),
    Style VARCHAR(20) UNIQUE
);

ALTER TABLE ART_OBJECT
    ADD CONSTRAINT ArtistName FOREIGN KEY (Artist) REFERENCES ARTIST(Name)

ALTER TABLE ART_OBJECT
    ADD CONSTRAINT EName FOREIGN KEY (Title) REFERENCES EXHIBITIONS(Name)

CREATE TABLE ARTIST
(
    Name VARCHAR(20),
    DateBorn DATE,
    Date_died DATE ,
    Country_of_origin VARCHAR(20),
    Epoch INT,
    Main_style VARCHAR(20),
    Description VARCHAR(20)
);

ALTER TABLE ARTIST
    ADD CONSTRAINT TimeMade FOREIGN KEY (Epoch) REFERENCES ART_OBJECT(Year)

CREATE TABLE PERMANENT_COLLECTION
(
    Date_acquired DATE,
    Status VARCHAR(20),
    Cost NUMBER,
    Year INT,
    ArtID NUMBER
);

ALTER TABLE PERMANENT_COLLECTION
    ADD CONSTRAINT TimeMade FOREIGN KEY (Year) REFERENCES ART_OBJECT(Year)

ALTER TABLE PERMANENT_COLLECTION
    ADD CONSTRAINT PCID FOREIGN KEY (ArtID) REFERENCES ART_OBJECT(Id_no)

CREATE TABLE BORROWED
(
    Id_no NUMBER,
    Name VARCHAR(20),
    Date_borrowed DATE,
    Date_returned DATE
);

ALTER TABLE BORROWED
    ADD CONSTRAINT BID FOREIGN KEY (Id_no) REFERENCES ART_OBJECT(Id_no)

ALTER TABLE BORROWED
    ADD CONSTRAINT BName FOREIGN KEY (Name) REFERENCES COLLECTIONS(CName)


CREATE TABLE PAINTING
(
    Paint_type VARCHAR(20),
    Drawn_on VARCHAR(20),
    Style VARCHAR(20),
    ArtID NUMBER
);

ALTER TABLE PAINTING
    ADD CONSTRAINT PStyle FOREIGN KEY (Style) REFERENCES ART_OBJECT(Style)

ALTER TABLE PAINTING
    ADD CONSTRAINT PID FOREIGN KEY (ArtID) REFERENCES ART_OBJECT(Id_no)

CREATE TABLE SCULPTURE
(
    Material VARCHAR(20),
    Height VARCHAR(20),
    Weight VARCHAR(20),
    Style VARCHAR(20),
    ArtID NUMBER
);

ALTER TABLE SCULPTURE
    ADD CONSTRAINT SCStyle FOREIGN KEY (Style) REFERENCES ART_OBJECT(Style)

ALTER TABLE PAINTING
    ADD CONSTRAINT SCID FOREIGN KEY (ArtID) REFERENCES ART_OBJECT(Id_no)


CREATE TABLE STATUE
(
    Material VARCHAR(20),
    Height VARCHAR(20),
    Weight VARCHAR(20),
    Style VARCHAR(20),
    ArtID NUMBER
);

ALTER TABLE STATUE
    ADD CONSTRAINT STStyle FOREIGN KEY (Style) REFERENCES ART_OBJECT(Style)

ALTER TABLE STATUE
    ADD CONSTRAINT STID FOREIGN KEY (ArtID) REFERENCES ART_OBJECT(Id_no)

CREATE TABLE OTHER
(
    Type VARCHAR(20),
    Style VARCHAR(20),
    ArtID NUMBER
);

ALTER TABLE OTHER
    ADD CONSTRAINT OStyle FOREIGN KEY (Style) REFERENCES ART_OBJECT(Style)

ALTER TABLE OTHER
    ADD CONSTRAINT OID FOREIGN KEY (ArtID) REFERENCES ART_OBJECT(Id_no)


CREATE TABLE COLLECTIONS
(
    CName VARCHAR(20) PRIMARY KEY,
    Type VARCHAR(20),
    Description VARCHAR(20),
    Address VARCHAR(20),
    Phone VARCHAR(20),
    Contact_person VARCHAR(20),
);

CREATE TABLE EXHIBITIONS
(
    Name VARCHAR(20) PRIMARY KEY,
    Start_date DATE,
    End_date DATE,
    E_id NUMBER,
);

ALTER TABLE EXHIBITIONS
    ADD CONSTRAINT EID FOREIGN KEY (E_id) REFERENCES ART_OBJECT(Id_no)


CREATE TABLE EXHIBITS_IN
(
    Id_no NUMBER,
    CName VARCHAR(20)
);

ALTER TABLE EXHIBITS_IN
    ADD CONSTRAINT EIID FOREIGN KEY (Id_no) REFERENCES ART_OBJECT(Id_no)

ALTER TABLE EXHIBITS_IN
    ADD CONSTRAINT EIName FOREIGN KEY (CName) REFERENCES COLLECTIONS(CName)

• 存在循环外键依赖：ART_OBJECT表的Artist字段关联ARTIST表的Name字段，但ARTIST表的Epoch字段又反过来关联ART_OBJECT表的Year字段，无论先插入哪张表的记录，都会因为另一张表没有对应关联记录触发约束报错。同时ART_OBJECT表的Title字段关联EXHIBITIONS表的Name字段，EXHIBITIONS表的E_id又关联ART_OBJECT表的Id_no字段，形成第二组循环依赖。
• 字段约束不符合业务逻辑：ART_OBJECT表的Year和Style字段都加了UNIQUE约束，意味着同一年份、同一种风格只能存在一个艺术品，完全不符合实际业务场景，也会导致关联时可选值极少。
• 表创建顺序错误：先创建了ART_OBJECT表，但它关联的ARTIST、EXHIBITIONS表都是后续才创建的，插入数据时会找不到关联表的对应记录。
• 外键添加笔误：给SCULPTURE表添加SCID外键时，语句误写为修改PAINTING表，导致约束绑定错误。

1. 调整不合理字段约束：删除ART_OBJECT表中Year和Style字段的UNIQUE约束，允许同一年份、同一种风格存在多个艺术品。
2. 解除循环外键依赖：删除ARTIST表关联ART_OBJECT.Year的TimeMade外键，艺术家所处年代直接存储数值即可，无需关联艺术品年份；删除EXHIBITIONS表关联ART_OBJECT.Id_no的EID外键，展览和艺术品的关联通过中间表EXHIBITS_IN实现即可，无需在展览表直接绑定艺术品ID。
3. 调整表创建顺序：按照「无依赖表优先」的原则调整创建顺序：先创建ARTIST、EXHIBITIONS、COLLECTIONS，再创建ART_OBJECT，最后创建PERMANENT_COLLECTION、BORROWED、PAINTING、SCULPTURE等关联ART_OBJECT的子表和EXHIBITS_IN中间表。
4. 修正外键添加笔误：将原语句中修改PAINTING表添加SCID约束的部分，改为修改SCULPTURE表。
5. 调整数据插入顺序：先插入ARTIST、EXHIBITIONS、COLLECTIONS这些被依赖的基础表数据，再插入ART_OBJECT表数据，最后插入子表和中间表数据。

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