CODEWITHSUNDEEP
sqloracle-databasedebuggingsyntaxsyntax-error
Easthombre
Easthombre

Reputation: 1

Trouble With ORA-00922 error in SQL Developer 17.2

I am currenrtly coding with SQL for a class project and have received the following error:

Error report - ORA-00922: missing or invalid option 00922. 00000 - "missing or invalid option" *Cause:
*Action: Error starting at line : 1 in command -

My code looks like this:

create table Admini(
    Admin_ID number(8) primary key, 
    EmployeeID number(8) references EmployeeInformation(EmployeeID), 
    BookingID number(8) references Booking(BookingID)
)

create table Booking(
    BookingID number(8) primary key, 
    CustomerID number(8) references Customer(CustomerID), 
    VehicleID number(8) references vehicle(VehicleID), 
    AdminID number(8) references Admini(AdminID), 
    BookingDate date not null, 
    BookingDestination varchar2(30) not null, 
    NumPassengers number(6) not null, 
    SecondDriverNeeded varchar2(5) check(SecondDriverNeeded in('TRUE','FALSE')), 
    DrivingDuration number(3) not null
)

create table Customer(
    CustomerID number(8) primary key, 
    CompanyName varchar2(30) not null, 
    ContactName varchar2(30) not null, 
    CustomerAddress varchar2(30) not null, 
    CustomerEmail varchar2(30) not null, 
    ContactNumber number(10) not null
)

create table Driver(
    DriverID number(8) primary key, 
    EmployeeID number(8) references Admini(AdminID), 
    DLicenseNum varchar2(16) references DLicense(DLicenseNum), 
    PCVSerialNum number(20) references PCV(PCVSerialNum), 
    CPCSerialNum number(20) references CPC(CPCSerialNum), 
    DrugTestID number(8) references DrugTest(DrugTestID), 
    DriverBreakLength number(3) not null
)

create table CPC(
    CPCSerialNum number(20) primary key, 
    CPCExpiryDate date not null
)

create table DLicense(
    DLicenseNum varchar2(16) primary key, 
    DLExpiryDate date not null, 
    PenaltyPoints number(2) not null
)

create table DrugTest(
    DrugTestID number(8) primary key, 
    TestDate date not null, 
    TestResults varchar2(8) check(TestResults in ('POSITIVE','NEGATIVE')), 
    TestType varchar2(7) check(TestType in ('DRUG','ALCOHOL','BOTH'))
)

create table EmployeeInformation(
    EmployeeID number(8) primary key, 
    EmployeeFirstName varchar2(30) not null, 
    EmployeeLastName varchar2(30) not null, 
    EmployeeAddress varchar2(30) not null, 
    EmployeeHomePhone number(10) not null, 
    EmployeeDOB date not null, 
    EmployeeGender varchar2(6) check(EmployeeGender in ('MALE','FEMALE','OTHER')), 
    EmployeePosition varchar2(6) check(EmployeePosition in ('ADMIN','DRIVER')), 
    EmployeeInsuranceNum varchar2(9) unique, 
    EmployeeSalary number(10) not null
)

create table PCV(
    PCVSerialNum number(20) primary key, 
    PCVExpiryDate date not null
)

create table Vehicle(
    VehicleID number(8) primary key, 
    DriverID number(8) references Driver(DriverID), 
    VehicleAvailable varchar2(5) check(VehicleAvailable('TRUE','FALSE')), 
    VehicleRate number(4) not null, 
    VehicleModel varchar2(10) not null, 
    VehicleMake varchar2(10) not null, 
    DateOfRegistration date not null, 
    RegistrationNumber number(8) unique, 
    VehicleColour varchar2(10) not null
)

What is causing my error?

Upvotes: 0

Views: 290

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22457

The tool you're using and how you're using said tool will determine if you need a ';' or not between statements.

One of your table's syntax is bad. 

create table Vehicle(
    VehicleID number(8) primary key, 
    DriverID number(8) references Driver(DriverID), 
    VehicleAvailable varchar2(5) check(VehicleAvailable IN ('TRUE','FALSE')), -- you forgot the IN keyword
    VehicleRate number(4) not null, 
    VehicleModel varchar2(10) not null, 
    VehicleMake varchar2(10) not null, 
    DateOfRegistration date not null, 
    RegistrationNumber number(8) unique, 
    VehicleColour varchar2(10) not null
);

Also, I recommend you make your FK constraints as ALTER TABLE ADD CONSTRAINTs vs in-line constraints. That way you can create all your tables, add the constraints, and you won't have to worry about circular references or getting the order JUST right in your scripts.

So something like this, but all of the alters at the end of your script. 

CREATE TABLE admini (
    admin_id     NUMBER(8) PRIMARY KEY,
    employeeid   NUMBER(8),
    bookingid    NUMBER(8) );

ALTER TABLE admini
    ADD CONSTRAINT admini_employeeinformation_fk FOREIGN KEY ( employeeid )
        REFERENCES employeeinformation ( employeeid )
    ENABLE;

ALTER TABLE admini
    ADD CONSTRAINT admini_booking_fk FOREIGN KEY ( bookingid )
        REFERENCES booking ( bookingid )
    ENABLE;

Upvotes: 0

Related Questions