Oracle SQL脚本执行报错求助：SP2系列错误排查

我编写了用于创建表及约束的Oracle SQL脚本，但在Run SQL Command Line中运行时完全无法执行，Oracle持续抛出以下错误：

SP2-0734: unknown command beginning "primary ke..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0042: unknown command ")" - rest of line ignored.

无法定位问题原因，请求协助排查。

原脚本代码：

create table flight (
    flight_Ref_No       VARCHAR(11)     NOT NULL,
    flightCrewList_ID   NUMBER(10)  NOT NULL,
    aircraft_Model_No   VARCHAR(6)  NOT NULL,
    flight_Date         DATE        NOT NULL,
    flight_Time         TIME        NOT NULL,
    Departure       VARCHAR(50)     NOT NULL,
    Arrival         VARCHAR(50)     NOT NULL,

primary key(flight_Ref_No),
foreign key(flightCrewList_ID) references crew_list(C_List_ID),
foreign key(aircraft_Model_No) references aircraft(aircraft_Model_No),
constraint chk_flight_Ref_No check (REGEXP_LIKE(flight_Ref_No, '^[A-Z]\d\d\d\d-\d\d\d\d$')),
constraint chk_CrewList_No check (REGEXP_LIKE(flightCrewList_ID, '^\d\d\d\d\d\d\d\d\d\d$')),
constraint chk_aircraft_Model_No check (REGEXP_LIKE(aircraft_Model_No, '^[A-Z][A-Z]\d\d\d\d$'))
);

create table aircraft (
    aircraft_Model_No   VARCHAR(6)  NOT NULL,
    aircraft_ID         VARCHAR(10)     NOT NULL,
    flight_Hrs      NUMBER(10,2)    NOT NULL,
    Registration_Num    NUMBER(15)  NOT NULL,
    Manufacturer        VARCHAR(20)     NOT NULL,
    Manufacture_Year    NUMBER(4)   NOT NULL,
    Status          CHAR(1)     NOT NULL,

primary key(aircraft_Model_No, aircraft_ID),
constraint chk_aircraft_Model_No check (REGEXP_LIKE(aircraft_Model_No, '^[A-Z][A-Z]\d\d\d\d$')),
constraint chk_aircraft_ID check (REGEXP_LIKE(aircraft_ID, '^[A-Z][A-Z][A-Z]\d\d\d\d\d\d\d$')),
constraint chk_Status check (UPPER(Status) in ('A','M'))
);

create table maintenance (
    maintenance_ID      NUMBER(10)  NOT NULL,
    aircraft_ID         VARCHAR(10)     NOT NULL,
    maintenance_Team    VARCHAR(5)  NOT NULL,
    Damaged_Parts       VARCHAR(50)     NOT NULL,
    Description         VARCHAR(50)     NOT NULL,

primary key(maintenance_ID),
foreign key(aircraft_ID) references aircraft(aircraft_ID),
constraint chk_maintenance_ID check (REGEXP_LIKE(maintenance_ID, '^\d\d\d\d\d\d\d\d\d\d$')),
constraint chk_aircraft_ID check (REGEXP_LIKE(aircraft_ID, '^[A-Z][A-Z][A-Z]\d\d\d\d\d\d\d$'))
);

create table booking (
    Acc_ID          VARCHAR(10)     NOT NULL,
    booking_ID      NUMBER(7)   NOT NULL,
    flight_Ref_No       VARCHAR(11)     NOT NULL,
    flight_Class        CHAR(1)     NOT NULL,
    Price           NUMBER(10,2)    NOT NULL,

primary key(Acc_ID, booking_ID),
foreign key(Acc_ID) references account(Acc_ID),
foreign key(flight_Ref_No) references flight(flight_Ref_No),
constraint chk_Acc_ID check (REGEXP_LIKE(Acc_ID, '^[A-Z][A-Z][A-Z][A-Z]--\d\d\d\d$')),
constraint chk_flight_Ref_No check (REGEXP_LIKE(flight_Ref_No, '^[A-Z]\d\d\d\d-\d\d\d\d$')),
constraint chk_flight_Class check (UPPER(flight_Class) in ('E','P','B','F'))
);

create table passenger (
    Passport        VARCHAR(9)  NOT NULL,
    passenger_IC        NUMBER(12)  NOT NULL,
    Seat_Num        VARCHAR(3)  NOT NULL,
    First_Name      VARCHAR(10) NOT NULL,
    Last_Name       VARCHAR(20) NOT NULL,
    Gender          CHAR(1)     NOT NULL,
    DOB         DATE        NOT NULL,
    Nationality     VARCHAR(3)  NOT NULL,
    Baggage_Weight      NUMBER(4,2) NOT NULL,
    
primary key(Passport, passenger_IC),
constraint chk_Passport check (REGEXP_LIKE(Passport, '^[A-Z][0-9]+$')),
constraint chk_Seat_Num check (REGEXP_LIKE(Seat_Num, '^[A-Z]\d\d$')),
constraint chk_Gender check (UPPER(Gender) in ('M','F')),
constraint chk_Nationality check (REGEXP_LIKE(Nationality, '^[A-Za-z][A-Za-z][A-Za-z]$'))
);

create table discount (
    discount_Rate       NUMBER(1,2) NOT NULL,
    Price           NUMBER(10,2)    NOT NULL,
    Condition_discount  VARCHAR(100),

primary key(discount_Rate),
foreign key(Price) references booking(Price)
);

create table account (
    Acc_ID          VARCHAR(10)     NOT NULL,
    Email           VARCHAR(35) NOT NULL,
    Password        VARCHAR(20) NOT NULL,
    Created_at      DATE        NOT NULL,
    Last_login_at       TIME        NOT NULL,
    Phone_Num       NUMBER(11)  NOT NULL,
    Acc_Name        VARCHAR(10) NOT NULL,
    Age         NUMBER(2)   NOT NULL,

primary key(Acc_ID, booking_ID),
constraint chk_Acc_ID check (REGEXP_LIKE(Acc_ID, '^[A-Z][A-Z][A-Z][A-Z]--\d\d\d\d$')),
constraint chk_Email check (REGEXP_LIKE(email,'^[a-zA-Z]\w+@(\S+)$'))
);

create table crew_list (
    C_List_ID       NUMBER(10)  NOT NULL,
    pilot_ID        NUMBER(4)   NOT NULL,
    Co-pilot_ID     NUMBER(4)   NOT NULL,
    Stewardess_1_ID     NUMBER(4)   NOT NULL,
    Stewardess_2_ID     NUMBER(4)   NOT NULL,
    Steward_1_ID        NUMBER(4)   NOT NULL,
    Steward_2_ID        NUMBER(4)   NOT NULL,

primary key(C_List_ID),
foreign key(pilot_ID) references pilot(pilot_ID),
foreign key(Co-pilot_ID) references pilot(pilot_ID),
foreign key(Stewardess_1_ID) references crew_cabin(Crew_ID),
foreign key(Stewardess_2_ID) references crew_cabin(Crew_ID),
foreign key(Steward_1_ID) references crew_cabin(Crew_ID),
foreign key(Steward_2_ID) references crew_cabin(Crew_ID),
constraint chk_CrewList_No check (REGEXP_LIKE(C_List_ID, '^\d\d\d\d\d\d\d\d\d\d$'))
);

create table pilot (
    pilot_ID        NUMBER(4)   NOT NULL,
    pilot_Rank      VARCHAR(10) NOT NULL,
    Total_flight_Hrs    NUMBER(10,2)    NOT NULL,
    pilot_Leave_Status  CHAR(1)     NOT NULL,

primary key(pilot_ID, pilot_Rank),
foreign key(pilot_ID) references worker(worker_ID),
constraint chk_pilot_Leave_Status check (UPPER(pilot_Leave_Status) in ('L','W'))
);

create table crew_cabin (
    Crew_ID         NUMBER(4)   NOT NULL,
    Crew_Rank       VARCHAR(10) NOT NULL,
    Working_Hrs     NUMBER(10,2)    NOT NULL,
    Crew_Leave_Status   CHAR(1)     NOT NULL,

primary key(Crew_ID, Crew_Rank),
foreign key(Crew_ID) references worker(worker_ID),
constraint chk_Crew_Leave_Status check (UPPER(Crew_Leave_Status) in ('L','W'))
);

create table worker (
    worker_ID       NUMBER(4)   NOT NULL,
    section_ID      NUMBER(2)   NOT NULL,
    W_First_Name        VARCHAR(10) NOT NULL,
    W_Last_Name     VARCHAR(20) NOT NULL,
    W_Gender        CHAR(1)     NOT NULL,
    W_Age           NUMBER(2)   NOT NULL,
    Date_of_Employment  DATE        NOT NULL,
    W_Email         VARCHAR(35) NOT NULL,
    W_Phone_Num     NUMBER(11)  NOT NULL,
    Salary          NUMBER(6,2) NOT NULL,

primary key(worker_ID),
foreign key(section_ID) references section(section_ID),
constraint chk_worker_Gender check (UPPER(W_Gender) in ('M','F'),
constraint chk_worker_Email check (REGEXP_LIKE(email,'^[a-zA-Z]\w+@(\S+)$'))
);

create table section (
    section_ID      NUMBER(2)   NOT NULL,
    section_Name        VARCHAR(20) NOT NULL,
    Description     VARCHAR(50) NOT NULL,

primary key(section_ID)
);

insert into section values(01, 'TESTING', 'TESTING');

commit;

错误原因及修正方案 #

1. SP2错误根源：SQL*Plus解析中断 #

SP2系列错误本质是SQL*Plus无法正确解析后续语句，通常是前面的SQL语法错误导致解析器状态异常，加上部分语法不符合Oracle规范，最终引发连锁报错。

2. 核心语法错误修正 #

• 不存在的TIME数据类型：Oracle没有原生TIME类型，将flight_Time、Last_login_at字段的TIME改为TIMESTAMP或DATE。
• 建表顺序违反外键依赖：外键依赖的表必须先创建，调整顺序为：section→worker→pilot/crew_cabin→crew_list→aircraft→flight→account→booking→passenger→maintenance→discount。
• 无效主键字段：account表主键包含booking_ID，但该字段不在表结构中，修正为主键Acc_ID。
• 约束括号未闭合：worker表的chk_worker_Gender约束缺少闭合括号，修正为：constraint chk_worker_Gender check (UPPER(W_Gender) in ('M','F'))。
• 外键引用非主键/非唯一字段：
  • maintenance表外键aircraft_ID引用aircraft的非主键字段，需给aircraft.aircraft_ID加唯一约束，或修改外键引用复合主键(aircraft_Model_No, aircraft_ID)。
  • discount表外键Price引用booking.Price，但Price不是主键/唯一字段，需调整逻辑（比如关联booking的主键Acc_ID, booking_ID）。
• 重复约束名：多个表使用相同约束名（如chk_aircraft_Model_No），需给约束名加表前缀，比如flight_chk_aircraft_Model_No、aircraft_chk_aircraft_Model_No。
• 数字字面量格式错误：insert into section values(01, ...)中的01会被解析为八进制数，改为1。

3. SQL*Plus执行建议 #

将脚本保存为.sql文件，在SQL*Plus中用@文件名.sql执行，避免逐行粘贴导致的分隔符识别问题。

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