SQL Error: ORA-00922: missing or invalid option

Question

CREATE TABLE chartered flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)

Should I not use CHAR data type?

I hear it is bad practice to use it but I wanted to make it so takeoff_at and destination have to have minimum 3 characters because they are airport codes.

This is the error I am getting:

Error at Command Line:1 Column:23
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

Solution 1

The error you're getting appears to be the result of the fact that there is no underscore between "chartered" and "flight" in the table name. I assume you want something like this where the name of the table is chartered_flight.

CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)

Generally, there is no benefit to declaring a column as CHAR(3) rather than VARCHAR2(3). Declaring a column as CHAR(3) doesn't force there to be three characters of (useful) data. It just tells Oracle to space-pad data with fewer than three characters to three characters. That is unlikely to be helpful if someone inadvertently enters an incorrect code. Potentially, you could declare the column as VARCHAR2(3) and then add a CHECK constraint that LENGTH(takeoff_at) = 3.

CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL CHECK( length( takeoff_at ) = 3 )
, destination CHAR (3) NOT NULL CHECK( length( destination ) = 3 )
)

Since both takeoff_at and destination are airport codes, you really ought to have a separate table of valid airport codes and define foreign key constraints between the chartered_flight table and this new airport_code table. That ensures that only valid airport codes are added and makes it much easier in the future if an airport code changes.

And from a naming convention standpoint, since both takeoff_at and destination are airport codes, I would suggest that the names be complementary and indicate that fact. Something like departure_airport_code and arrival_airport_code, for example, would be much more meaningful.

Solution 2

You should not use space character while naming database objects. Even though it's possible by using double quotes(quoted identifiers), CREATE TABLE "chartered flight" ..., it's not recommended. Take a closer look here

Solution 3

there's nothing wrong with using CHAR like that.. I think your problem is that you have a space in your tablename. It should be: charteredflight or chartered_flight..

Comments

[Jatinder Singh]

nice information very interesting but seeing as the airport code thing is just added by me and not in the coursework spec do you think I should add that in? I would have to change my ERD to match. Where would I add the CHECK constraint is that during the table creation?

[Justin Cave]

@JatinderSingh - I added an example of declaring the CHECK constraints to the table definition (you can also add them afterwards in separate ALTER TABLE commands). My bias would always be to do something correctly if you're going to do it which would entail adding the additional table if you're going to add airport codes to the chartered_flight table. But, obviously, it's up to you to determine how much work you want to put in to an assignment, whether the instructor will care, and whether the extra work helps you learn anything.

[Jatinder Singh]

OK say I was going to implement the separate table for airport codes then how would the two link? do I make departure_airport_code and arrival foreign keys of one column airport_code in the airport relation?

[Jatinder Singh]

also would that relationship between charted flight and airport be a many to many type? would it need to be resolved? @Justin Cave

[Justin Cave]

@JatinderSingh - You could either let airport_code be the primary key of the new airport table and create a foreign key constraint on both the departure_airport_code and arrival_airport_code columns or you could create a sequence-generated primary key on the airport table called airport_id, define the columns in chartered_flight to be departure_airport_id and arrival_airport_id and create the foreign key constraints. The airport_id approach is more generic but if airport codes are more like state codes that never really change, using the code as the key is more practical.

[Jatinder Singh]

YES if you look on the internet the codes are real codes they are static not something I want the database to create so I have made airport_code the PK