ORACLE SQL script does not create table nor develop the constraints

Question

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;

The code above does not work at all, and i couldnt figure out why. As i was trying to run the script in Run SQL Command Line, oracle kept prompting the following messages

SP2-0734: unknown command beginning "primary ke..." - rest of line ignored. SP2-0734: unknown command beginning "constraint..." - rest of line ignored. SP2-0734: unknown command beginning "constraint..." - rest of line ignored. SP2-0734: unknown command beginning "constraint..." - 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. SP2-0734: unknown command beginning "primary ke..." - rest of line ignored. SP2-0734: unknown command beginning "foreign ke..." - rest of line ignored. SP2-0734: unknown command beginning "constraint..." - rest of line ignored. SP2-0734: unknown command beginning "constraint..." - rest of line ignored.

Answer 1

If you're running this in SQL*Plus, as you appear to be, either get rid of the blank lines before the primary key ... lines, or do:

set sqlblanklines on

at the start of the script to change the behaviour.

By default SQL*Plus interprets a blank line as the end of the statement, but doesn't run it. So the first part of each entire create table command is essentially ignored, and the second part starting primary key upto the ; statement separated is treated as a standalone command. Hence the error message you get, since those parts are not valid on their own.

From the documentation:

SET SQLBL[ANKLINES] {ON | OFF}
Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

---

Once you've got past that, Oracle doesn't have a TIME data type. The DATE data type stores dates and times, down to second precision (as described in the documentation, so you don't need a separate column for the time, and it would just complicate things if you tried to keep them separate. That means you can replace

    flight_Date         DATE        NOT NULL,
    flight_Time         TIME        NOT NULL,

with

    flight_DateTime     DATE        NOT NULL,

and store the full date and time together. You could also use TIMESTAMP or one of its variants. (You probably either need to store all values converted to UTC, or use a time zone-aware data type.)

---

now sql says that i am referencing to a non-existing table, but i've already written the sql statement for the tables

As one example, table flight references tables crew_list and aircraft in its foreign key definitions; but you haven't created those tables yet - they are created later in the script. The statements are run in the order they appear in the script, and you can refer to objects that are later in the script as they don't exist and Oracle doesn't know they are going to.

You either need to change the order you create the tables so everything is consistent; or perhaps more simply, create all of the tables without any foreign keys, then at the end of the script, once they all exist, add the foreign keys to all of the tables, with alter table statements.