CODEWITHSUNDEEP
sqloracle-databaseforeign-keyscreate-table
Nii Odai
Nii Odai

Reputation: 9

ORA-00906: missing left parenthesis oracle error probably because I messed up the syntax

So this is a table I am trying to create in relation to another table in my database. I know I have messed up the syntax somewhere, I'm just not sure where because oracle doesn't point out the exact line of error. Any help will be appreciated. And yes I'm new to this

CREATE TABLE USERS (
    USER_ID NUMBER(5) PRIMARY KEY,
    NAME VARCHAR2(255), 
    YEAR_OF_BIRTH YEAR, 
    SEX CHAR(2), 
    ADDRESS VARCHAR2(255), 
    PHONE_NUMBER VARCHAR2(20), 
    CA_ID NUMBER(5),
    FOREIGN KEY CA_ID REFERENCES CANDIDATE(CANDIDATE_ID) ON DELETE NO ACTION
);

Upvotes: 0

Views: 83

Answers (1)

MT0
MT0

Reputation: 167972

You need to:

  • Change YEAR_OF_BIRTH YEAR to have a valid data type as YEAR is not a valid data type.
  • Have braces around the column(s) you are declaring the foreign key on.
  • ON DELETE NO ACTION is not syntactically correct. You cannot take no action if the item being referenced is deleted as then the constraint will be violated; instead you need to either set the value to NULL or delete the record using CASCADE or omit the ON DELETE clause if you do not want the parent record to be able to be deleted if this child exists and references the parent.

Like this:

CREATE TABLE USERS (
  USER_ID       NUMBER(5) PRIMARY KEY,
  NAME          VARCHAR2(255), 
  YEAR_OF_BIRTH NUMBER(4), 
  SEX           CHAR(2), 
  ADDRESS       VARCHAR2(255), 
  PHONE_NUMBER  VARCHAR2(20), 
  CA_ID         NUMBER(5),
  FOREIGN KEY ( CA_ID ) REFERENCES CANDIDATE(CANDIDATE_ID ) ON DELETE SET NULL
);

db<>fiddle

Upvotes: 2

Related Questions