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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Error: ORA-00922: missing or invalid option

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to oracle-sqldeveloper