Attempting to create a table with composite keys and date and time data types. Only get errors

Question

OK so here is the table and data types I am attempting to create in sql developer:

 Table_Name interview
 Field_Name e_number, pos_id, date, time, awarded
 Data_Type  varchar2(9), varchar2(15), date, number(9), varchar2(3) 
 Nulls_Allowed  
 Primary_Key    y, y, n, n, n
 Unique         y, y, n, n, n
 Foreign_Key    
 Comments

Here is the sql I am trying to use:

 CREATE TABLE interview(
 e_number      VARCHAR2(9) NOT NULL UNIQUE,
 pos_id        VARCHAR2(15) NOT NULL UNIQUE,
 date          DATE NOT NULL,
 time          NUMBER(8) NOT NULL,
 awarded       VARCHAR2(3),
 CONSTRAINT pk_interview PRIMARY KEY (e_number, pos_id),
 CONSTRAINT fk_e_number FOREIGN KEY (e_number) REFERENCES student
    (e_number),
 CONSTRAINT fk_pos_id FOREIGN KEY (pos_id) REFERENCES position
    (pos_id)

Can someone to tell me where I have "invalid identifier"? Cause I am completely lost.

Welcome to Stack Overflow. What is the complete error message? Does it include line and column information to direct you to the actual "invalid identifier"? — Remy Lebeau, Apr 12 '15 at 03:22
Error starting at line : 20 in command - CREATE TABLE interview( e_number VARCHAR2(9) NOT NULL UNIQUE, pos_id VARCHAR2(15) NOT NULL UNIQUE, date DATE NOT NULL, time NUMBER(8) NOT NULL, awarded VARCHAR2(3), CONSTRAINT pk_interview PRIMARY KEY (e_number, pos_id), CONSTRAINT fk_e_number FOREIGN KEY (e_number) REFERENCES student (e_number), CONSTRAINT fk_pos_id FOREIGN KEY (pos_id) REFERENCES position (pos_id) ) Error report - SQL Error: ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier" — CLifford, Apr 12 '15 at 03:25
Your `CREATE` statement is not 20 lines long, so what is actually on line 20 exactly? — Remy Lebeau, Apr 12 '15 at 03:27
The create table starts at line 20, before it is only the comments I have to have. — CLifford, Apr 12 '15 at 03:28
`date` and `time` are reserved keywords, so try wrapping those column names in double-quotes, eg: `"date" DATE NOT NULL`, `"time" NUMBER(8) NOT NULL`. See http://stackoverflow.com/questions/1162381/how-do-i-escape-a-reserved-word-in-oracle — Remy Lebeau, Apr 12 '15 at 03:29
Now it is telling me: "Error report - SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: — CLifford, Apr 12 '15 at 03:35
and add a closing right-parenthesis and semicolon to close the create table scope — amdixon, Apr 12 '15 at 04:45

score 1 · Answer 1 · edited May 23 '17 at 12:06

1

CREATE TABLE interview(
    e_number   VARCHAR2(9) NOT NULL UNIQUE,
    pos_id     VARCHAR2(15) NOT NULL UNIQUE,
    "date"     DATE NOT NULL,
    "time"     NUMBER(8) NOT NULL,
    awarded    VARCHAR2(3),
    CONSTRAINT pk_interview PRIMARY KEY (e_number, pos_id),
    CONSTRAINT fk_e_number FOREIGN KEY (e_number) REFERENCES student (e_number),
    CONSTRAINT fk_pos_id FOREIGN KEY (pos_id) REFERENCES position (pos_id)
);

As suggested by @Remy_Lebeau, @CLifford, and @amdixon

date and time are reserved words (see this post), so enclose them in quotations. Also, add right parentheses and semicolon to close your table declaration.

edited May 23 '17 at 12:06

Community

1
1

answered Apr 12 '15 at 06:10

Rob Wise

4,930
3
26
31

I have the right parentheses and semicolon, but I was still getting "missing right parentheses" error. I was able to create the table but considering I was supposed to create the table with all constraints in the sql syntax I will probably get points taken off and I would like to know how to do this for future references and lab deliverable. Since I had the right parentheses why would it give me the error. – CLifford Apr 12 '15 at 07:11
Are you 100% sure this error is not coming from another part of your code? This is [running on SQL Fiddle](http://sqlfiddle.com/#!4/69470). – Rob Wise Apr 12 '15 at 07:19
Tried adding a second right parentheses to double check and counted all the others, then tested several other combinations with the same error. – CLifford Apr 12 '15 at 07:23

Attempting to create a table with composite keys and date and time data types. Only get errors

1 Answers1