Oracle Apex中SQL脚本执行报错求助（含代码与错误日志）

核心错误点 #

• 外键依赖顺序错误：创建Members表时，外键引用的Students和Teachers表尚未创建，Oracle不支持引用不存在的对象，导致外键约束创建失败。
• 日期格式不匹配：Rentals插入语句中的日期字符串格式错误，例如'2023-15-12'不符合YYYY-MM-DD的格式（月份不能为15），属于无效日期值。
• 重复定义唯一约束：Books表创建时已为ISBN和BarcodeNumber字段添加UNIQUE约束，后续ALTER TABLE再次添加同名约束会触发重复约束错误。
• 无表存在检查的删除语句：初始DROP TABLE语句未做存在性检查，若表不存在会直接报错，Oracle 12c+可通过IF EXISTS规避。

修正后的完整脚本 #

-- Drop all tables if they exist (Oracle 12c+支持IF EXISTS)
DROP TABLE Penalties IF EXISTS;
DROP TABLE Rentals IF EXISTS;
DROP TABLE Books IF EXISTS;
DROP TABLE Teachers IF EXISTS;
DROP TABLE Students IF EXISTS;
DROP TABLE Members IF EXISTS;
DROP TABLE BookTypes IF EXISTS;
DROP TABLE BookCategories IF EXISTS;
DROP TABLE Publishers IF EXISTS;
DROP TABLE Authors IF EXISTS;

-- 先创建被依赖的表：Authors, Publishers, BookCategories, BookTypes, Students, Teachers
-- Authors
CREATE TABLE "Authors" (
    "AuthorID" NUMBER PRIMARY KEY,
    "AuthorName" VARCHAR2(100) NOT NULL
);

-- Publishers
CREATE TABLE "Publishers" (
    "PublisherID" NUMBER PRIMARY KEY,
    "PublisherName" VARCHAR2(100) NOT NULL
);

-- Book Categories
CREATE TABLE "BookCategories" (
    "CategoryID" NUMBER PRIMARY KEY,
    "CategoryName" VARCHAR2(50) NOT NULL
);

-- Book Types
CREATE TABLE "BookTypes" (
    "BookTypeID" NUMBER PRIMARY KEY,
    "BookTypeName" VARCHAR2(50) NOT NULL
);

-- Students
CREATE TABLE "Students" (
    "StudentNumber" NUMBER PRIMARY KEY,
    "Department" VARCHAR2(100) NOT NULL
);

-- Teachers
CREATE TABLE "Teachers" (
    "TeacherNumber" NUMBER PRIMARY KEY,
    "Age" NUMBER NOT NULL
);

-- Members (现在Students和Teachers已存在，外键可正常创建)
CREATE TABLE "Members" (
    "MemberID" NUMBER PRIMARY KEY,
    "FirstName" VARCHAR2(50) NOT NULL,
    "MiddleName" VARCHAR2(60) NOT NULL,
    "LastName" VARCHAR2(50) NOT NULL,
    "BookLimit" NUMBER CHECK ("BookLimit" >= 1 AND "BookLimit" <= 5) NOT NULL,
    "DonationHistory" VARCHAR2(255),
    "PenaltyStatus" NUMBER NOT NULL,
    "MembershipNumber" NUMBER NOT NULL,
    "StudentNumber" NUMBER,
    "TeacherNumber" NUMBER,
    CONSTRAINT "FK_StudentNumber" FOREIGN KEY ("StudentNumber") REFERENCES "Students"("StudentNumber"),
    CONSTRAINT "FK_TeacherNumber" FOREIGN KEY ("TeacherNumber") REFERENCES "Teachers"("TeacherNumber")
);

-- Books
CREATE TABLE "Books" (
    "BookID" NUMBER PRIMARY KEY,
    "Title" VARCHAR2(255) NOT NULL,
    "PublisherID" NUMBER,
    "AuthorID" NUMBER,
    "CategoryID" NUMBER,
    "BookTypeID" NUMBER,
    "ISBN" VARCHAR2(13) UNIQUE,
    "PublicationYear" NUMBER,
    "PrintDate" DATE,
    "BarcodeNumber" VARCHAR2(20) UNIQUE,
    CONSTRAINT "FK_PublisherID" FOREIGN KEY ("PublisherID") REFERENCES "Publishers"("PublisherID"),
    CONSTRAINT "FK_AuthorID" FOREIGN KEY ("AuthorID") REFERENCES "Authors"("AuthorID"),
    CONSTRAINT "FK_CategoryID" FOREIGN KEY ("CategoryID") REFERENCES "BookCategories"("CategoryID"),
    CONSTRAINT "FK_BookTypeID" FOREIGN KEY ("BookTypeID") REFERENCES "BookTypes"("BookTypeID")
);

-- Rentals
CREATE TABLE "Rentals" (
    "RentalID" NUMBER PRIMARY KEY,
    "BookID" NUMBER,
    "MemberID" NUMBER,
    "RentalDate" DATE,
    "ReturnDate" DATE,
    "RentalNumber" NUMBER,
    CONSTRAINT "FK_BookID" FOREIGN KEY ("BookID") REFERENCES "Books"("BookID"),
    CONSTRAINT "FK_MemberID" FOREIGN KEY ("MemberID") REFERENCES "Members"("MemberID")
);

-- Penalty Records
CREATE TABLE "Penalties" (
    "PenaltyID" NUMBER PRIMARY KEY,
    "RentalID" NUMBER,
    "PenaltyAmount" NUMBER(10, 2),
    CONSTRAINT "FK_RentalID" FOREIGN KEY ("RentalID") REFERENCES "Rentals"("RentalID")
);

-- Modifications (移除重复的唯一约束添加操作，保留必要修改)
ALTER TABLE "Members" DROP CONSTRAINT "FK_StudentNumber";
ALTER TABLE "Members" DROP CONSTRAINT "FK_TeacherNumber";

ALTER TABLE "Members" ADD CONSTRAINT "FK_StudentNumber" FOREIGN KEY ("StudentNumber") REFERENCES "Students"("StudentNumber");
ALTER TABLE "Members" ADD CONSTRAINT "FK_TeacherNumber" FOREIGN KEY ("TeacherNumber") REFERENCES "Teachers"("TeacherNumber");

-- 修改PenaltyAmount精度（若需要可启用）
--ALTER TABLE "Penalties" MODIFY PenaltyAmount NUMBER(12, 2);

ALTER TABLE "Penalties" DROP CONSTRAINT "FK_RentalID";
ALTER TABLE "Penalties" ADD CONSTRAINT "FK_RentalID" FOREIGN KEY ("RentalID") REFERENCES "Rentals"("RentalID");

-- Drop and Recreate the Members table
DROP TABLE "Members";

CREATE TABLE "Members" (
    "MemberID" NUMBER PRIMARY KEY,
    "FirstName" VARCHAR2(50) NOT NULL,
    "LastName" VARCHAR2(50) NOT NULL,
    "BookLimit" NUMBER CHECK ("BookLimit" >= 1 AND "BookLimit" <= 5) NOT NULL,
    "DonationHistory" VARCHAR2(255),
    "PenaltyStatus" NUMBER(1, 0) NOT NULL,
    "MembershipNumber" NUMBER NOT NULL,
    "StudentNumber" NUMBER,
    "TeacherNumber" NUMBER,
    CONSTRAINT "FK_StudentNumber" FOREIGN KEY ("StudentNumber") REFERENCES "Students"("StudentNumber"),
    CONSTRAINT "FK_TeacherNumber" FOREIGN KEY ("TeacherNumber") REFERENCES "Teachers"("TeacherNumber")
);

-- Data

-- Authors
INSERT INTO "Authors" ("AuthorID", "AuthorName")
VALUES
    (1, 'Emrah Safa Gürkan'),
    (2, 'Namık Kemal'),
    (3, 'Christy Brown');

-- Publishers
INSERT INTO "Publishers" ("PublisherID", "PublisherName")
VALUES
    (1, 'Sky Publishing'),
    (2, 'Ephesus Publications'),
    (3, 'Red Cat Publishing');

-- Book Categories
INSERT INTO "BookCategories" ("CategoryID", "CategoryName")
VALUES
    (1, 'History'),
    (2, 'Literature'),
    (3, 'Poetry');

-- Book Types
INSERT INTO "BookTypes" ("BookTypeID", "BookTypeName")
VALUES
    (1, 'Physical Book'),
    (2, 'E-book'),
    (3, 'Audiobook');

-- Students
INSERT INTO "Students" ("StudentNumber", "Department")
VALUES
    (101, 'IT'),
    (102, 'BTBS'),
    (103, 'BTEP');

-- Teachers
INSERT INTO "Teachers" ("TeacherNumber", "Age")
VALUES
    (201, 34),
    (202, 43),
    (203, 68);

-- Members
INSERT INTO "Members" ("MemberID", "FirstName", "LastName", "BookLimit", "PenaltyStatus", "MembershipNumber", "StudentNumber")
VALUES
    (1, 'Mehmet', 'Akif', 3, 0, 1001, 101),
    (2, 'Sezer', 'Yazıcı', 5, 1, 1002, NULL),
    (3, 'Nilgün', 'Çarıkçı', 2, 0, 1003, 103);

-- Books
INSERT INTO "Books" ("BookID", "Title", "PublisherID", "AuthorID", "CategoryID", "BookTypeID", "ISBN", "PublicationYear", "PrintDate", "BarcodeNumber")
VALUES
    (1, 'Clouds', 1, 1, 1, 1, '1234567890123', 1978, TO_DATE('1978-02-01', 'YYYY-MM-DD'), 'B12345'),
    (2, 'Book2', 2, 2, 2, 2, '2345678901234', 2022, TO_DATE('2022-02-15', 'YYYY-MM-DD'), 'B67890'),
    (3, 'Book3', 3, 3, 3, 3, '3456789012345', 2006, TO_DATE('2006-03-30', 'YYYY-MM-DD'), 'B98765');

-- Rentals (修正日期格式，将日和月位置调整正确)
INSERT INTO "Rentals" ("RentalID", "BookID", "MemberID", "RentalDate", "ReturnDate", "RentalNumber")
VALUES
    (1, 1, 1, TO_DATE('2023-11-11', 'YYYY-MM-DD'), TO_DATE('2023-12-11', 'YYYY-MM-DD'), 101),
    (2, 2, 2, TO_DATE('2023-11-12', 'YYYY-MM-DD'), TO_DATE('2023-12-15', 'YYYY-MM-DD'), 102),
    (3, 3, 3, TO_DATE('2023-12-10', 'YYYY-MM-DD'), TO_DATE('2023-10-16', 'YYYY-MM-DD'), 103);

-- Penalties
INSERT INTO "Penalties" ("PenaltyID", "RentalID", "PenaltyAmount")
VALUES
    (1, 1, 0.00),   -- No penalty for Member 1
    (2, 2, 5.00),   -- Member 2 penalized with 5 ₺
    (3, 3, 0.00);   -- Member 3 did not receive a penalty

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