SQL Error: ORA-00907: missing right parenthesis struggling

Question

CREATE TABLE ADMIN (
    A_EMP_ID                   CHAR 5 BYTE                            NOT NULL,
    ADMIN_START_DATE           DATE                 DEFAULT SYSDATE       NOT NULL,
    ADMIN_END_DATE             DATE                                       NULL,
    DIVERSITY_TRAINING_CERT      CHAR(1 BYTE)       DEFAULT 'N'           NOT NULL,
    ADMIN_TITLE                  CHAR(40 BYTE)                            NULL,
    CONSTRAINT                   ADMIN_PK           PRIMARY KEY(A_EMP_ID),
  CONSTRAINT        ADMIN_FK1          
  FOREIGN KEY(A_EMP_ID)
  REFERENCES ADMIN(A_EMP_ID),
  CONSTRAINT                 ADMIN_DIVERSITY_CERT
      CHECK (DIVERSITY_TRAINING_CERT = 'N','Y'),
  CONSTRAINT                 ADMIN_END_DATE
      CHECK (<= 'ADMIN_START_DATE'),
    );



Error starting at line : 1 in command -
CREATE TABLE ADMIN (
    A_EMP_ID                   CHAR 5 BYTE                            NOT NULL,
    ADMIN_START_DATE           DATE                 DEFAULT SYSDATE       NOT NULL,
    ADMIN_END_DATE             DATE                                       NULL,
    DIVERSITY_TRAINING_CERT      CHAR(1 BYTE)       DEFAULT 'N'           NOT NULL,
    ADMIN_TITLE                  CHAR(40 BYTE)                            NULL,
    CONSTRAINT                   ADMIN_PK           PRIMARY KEY(A_EMP_ID),
  CONSTRAINT        ADMIN_FK1          
  FOREIGN KEY(A_EMP_ID)
  REFERENCES ADMIN(A_EMP_ID),
  CONSTRAINT                 ADMIN_DIVERSITY_CERT
      CHECK (DIVERSITY_TRAINING_CERT = 'N','Y'),
  CONSTRAINT                 ADMIN_END_DATE
      CHECK (<= 'ADMIN_START_DATE'),
    )
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

The top part is my code and when I run it, I get the message from the bottom half. I'm thinking it's something to do with my foreign key, but I don't know what the exact solution is. Any help is greatly appreciated.

Sorry I was trying to put on an image of it but it doesn't seem to be working. I put the code back on. It's spaced wrong on here when I copy and paste it from Oracle. — Barry Robinholt, Nov 10 '15 at 17:59
please update your code with your changes so we don't have to squint in the comments — kevinskio, Nov 10 '15 at 18:12

score 3 · Answer 1 · 2015-11-10T18:19:32.447

3

There is a lot wrong with your statement.

A_EMP_ID CHAR 5 BYTE is missing the (..) around the length constraint
You most definitely do not want to use CHAR for the admin_title. UseVARCHAR2 instead.
DIVERSITY_TRAINING_CERT = 'N','Y' is not a valid expression. You probably want diversity_training_cert IN ('N','Y')
While FOREIGN KEY (a_emp_id) REFERENCES admin(a_emp_id) is syntactically correct, it doesn't makes sense. I guess you want a manager_id or something similar. And then something like FOREIGN KEY (manager_id) REFERENCES admin(a_emp_id).
Alternatively you maybe intended to reference an employee table. In that case the a_emp_id data type must match the type of PK column that table.
CONSTRAINT ADMIN_END_DATE CHECK (<= 'ADMIN_START_DATE'), has three errors:
- a column must not be enclosed in single quotes. So it has to be admin_start_date not 'admin_start_date'
- a check constraint requires a proper condition. <= admin_start_date is not a condition, you need to compare the column it with something. Presumable admin_end_date
- you have a comma , after that expression which is wrong as well.

Putting it all together you get:

CREATE TABLE admin 
(
    a_emp_id                  CHAR(5 BYTE) NOT NULL, 
    admin_start_date          DATE DEFAULT SYSDATE NOT NULL,
    admin_end_date            DATE NULL,
    diversity_training_cert   CHAR(1 BYTE)  DEFAULT 'N' NOT NULL,
    admin_title               VARCHAR2(40 BYTE) NULL,
    CONSTRAINT admin_pk 
        PRIMARY KEY(a_emp_id),
    CONSTRAINT admin_fk1 
        FOREIGN KEY (a_emp_id) REFERENCES admin(a_emp_id),
    CONSTRAINT admin_diversity_cert 
        CHECK (diversity_training_cert IN ('N','Y')), 
    CONSTRAINT admin_end_date 
        CHECK ( admin_end_date <= admin_start_date) 
);

Unrelated, but: there is also absolutely no need to write everything in upper case.

edited Nov 10 '15 at 18:19

answered Nov 10 '15 at 18:12

Another thing that I recommended on my previous deleted answer (since you did it first) was to use a number type to the ID field so he can use a sequence to fill it. +1 – Jorge Campos Nov 10 '15 at 18:14
@JorgeCampos: you don't know how an emp_id is defined. It might be something like `XY-42`. Using `char` for that is not necessarily wrong (but it most definitely is for the title column) – Nov 10 '15 at 18:16
3

@JorgeCampos - except that is also an FK, so the related `admin` record... wait, that FK is referring to itself? Huh? I would have expected an FK out to, say, an employee table - so the admin has to be an employee. In which case the data type of *that* would have to match, which was the point I was going to make - that the data type of `a_emp_id` can't be changed in isolation. (But number might still be appropriate for both tables, of course). – Alex Poole Nov 10 '15 at 18:17
@AlexPoole: I guess the table misses a column "manager_id" or something similar. – Nov 10 '15 at 18:18
kkkkkkkkkkkkkkkkkkkkkkkkkkkkk @AlexPoole very good... missed that! – Jorge Campos Nov 10 '15 at 18:18
There are a lot of discussion here about using a varchar field as a primary key and most of then (even though not considering the context of the OP's solution) suggest that it is not a good pratice to do it. I particulary like this answer from @Remus http://stackoverflow.com/a/1301536/460557 on that matter. Therefore it would be just a suggestion, maybe the OP is just starting considering the amount of error on his code and he doesn't even know that. – Jorge Campos Nov 10 '15 at 18:24
@JorgeCampos: well the linked answer is for SQL Server and there it indeed does make a difference because of the clustered index that the PK essentially is. If you have a column that is unique and "stable" to be a PK, add _another_ unique column (with _another_ index to maintain) doesn't really buy you that much on Oracle. There might be situations where the size matters especially when the column is referenced a lot - but this is getting off topic (and opinion based) – Nov 10 '15 at 18:38
@a_horse_with_no_name i read the link too but concept related does that apply to oracle ? Especially the thing related to archive data and clustered? – Moudiz Nov 10 '15 at 20:40

score 1 · Answer 2 · answered Nov 10 '15 at 17:59

1

You have a pending comma before the final parenthesis.

answered Nov 10 '15 at 17:59

NoAnOld

171
6

CHECK (<= 'ADMIN_START_DATE') but still came back missing right parenthesis – Barry Robinholt Nov 10 '15 at 18:01

score 1 · Answer 3 · answered Nov 10 '15 at 18:01

1

CHAR 5 BYTE should be CHAR (5 BYTE) (but CHAR should not be used anyway try VARCHAR2 or NVARCHAR2...)

and the constraint <= 'ADMIN_START_DATE' is incorrect. This should have two values to compare

answered Nov 10 '15 at 18:01

kevinskio

4,431
1
22
36

CREATE TABLE ADMIN ( A_EMP_ID CHAR (5 BYTE) NOT NULL, ADMIN_START_DATE DATE DEFAULT SYSDATE NOT NULL, ADMIN_END_DATE DATE NULL, DIVERSITY_TRAINING_CERT CHAR(1 BYTE) DEFAULT 'N' NOT NULL, ADMIN_TITLE CHAR(40 BYTE) NULL, CONSTRAINT ADMIN_PK PRIMARY KEY(A_EMP_ID), CONSTRAINT ADMIN_FK1 FOREIGN KEY(A_EMP_ID) – Barry Robinholt Nov 10 '15 at 18:10
REFERENCES ADMIN(A_EMP_ID), CONSTRAINT ADMIN_DIVERSITY_CERT CHECK (DIVERSITY_TRAINING_CERT = 'N','Y'), CONSTRAINT CHECK ('ADMIN_END_DATE' >= 'ADMIN_START_DATE') ); – Barry Robinholt Nov 10 '15 at 18:11

SQL Error: ORA-00907: missing right parenthesis struggling

3 Answers3

Linked