SQL Error: ORA-00907: missing right parenthesis
Solution 1
To specify foreign key constraint, you should either use inline customer_id [type] REFERENCES [master_table_name]([master_column_name])
or out of line syntax : , CONSTRAINT [constraint_name] FOREIGN KEY(customer_id) REFERENCES [master_table_name]([master_column_name])
See more example here. Also, it usually makes sense to add indexes on foreign key columns.
For flight_time you probably need to use INTERVAL DAY TO SECOND
type
Solution 2
There is no type called TO_DATE. To_DATE is used to convert a string to a date.
So the create statement should be something like
CREATE TABLE chartered_flight(
flight_no NUMBER(4) PRIMARY KEY,
customer_id FOREIGN KEY,
aircraft_no FOREIGN KEY,
flight_type VARCHAR2 (12),
flight_date DATE NOT NULL,
flight_time VARCHAR(4) NOT NULL,
takeoff_at CHAR (3) NOT NULL,
destination CHAR (3) NOT NULL)
You can use to_date when selecting data from the table. For example
Select to_date(flight_date, 'yyyy/mm/dd') from chartered_flight;
Jatinder Singh
Updated on June 04, 2022Comments
-
Jatinder Singh almost 2 years
I am using Oracle SQL developer to create a basic table with the following command:
CREATE TABLE chartered_flight( flight_no NUMBER(4) PRIMARY KEY , customer_id FOREIGN KEY , aircraft_no FOREIGN KEY , flight_type VARCHAR2 (12) , flight_date DATE NOT NULL , flight_time TO_DATE 'HH:MI' NOT NULL , takeoff_at CHAR (3) NOT NULL , destination CHAR (3) NOT NULL )
Where is the missing right parenthesis? Or is the syntax that I have used incorrect.
I have made the following changes:
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)
Now I get this error:
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:
I have a feeling it is something to do with TO_DATE or is it because I have not created my aircraft table yet so aircraft_no is missing? Can some one please help, thanks.
-
Jatinder Singh over 12 yearsbut what if I want the time to be specific to just HH:MM please how do I set this? I see default oracle data type for date is just DD/MM/YY
-
ziggy over 12 yearsYou can possibly declare flight_time as varchar2 and then use a check constraint to validate the data.
-
Ben over 12 yearsWouldn't it be better to have a flight_time as a number in minutes, if that is the lowest unit of measurement you want to use, and then you can convert it into hours / days in code. I definitely wouldn't have a number stored as a varchar if at all possible. Bound to cause trouble.
-
Jatinder Singh over 12 yearswhere would I implement the check constraint? I think this is OTT maybe I should just leave it date as this is only for coursework.
-
ziggy over 12 years@Ben yes i agree but he wants to include the ':'. I agree that number format can easily be formatted.
-
Jatinder Singh over 12 yearsI have made changes that you have suggested but now I get a new error please can you have a look thanks.
-
a1ex07 over 12 yearsYou don't need square brackets in the
CREATE TABLE
. In my answer I use them to specify that you have to put actual values, so you needcustomer_id NUMBER(6) REFERENCES customer(customer_id)
-
Jatinder Singh over 12 yearsI have made that change but I am still getting the exact same error?! @a1ex07