CODEWITHSUNDEEP
sqloracle-database
no-Name-Is-Still-A-Name
no-Name-Is-Still-A-Name

Reputation: 91

oracle SQL - ORA-009007 Missing right parenthesis

I have this sql

CREATE TABLE PATIENTS
(
patientID   NUMBER NOT NULL,
healthInsID NUMBER,
fname       VARCHAR(20) NOT NULL,
minit       VARCHAR(15),
lname       VARCHAR(30) NOT NULL,
gender      CHAR(2),
email       VARCHAR(40),
street      VARCHAR(40),
postalCode  NUMBER,
city        VARCHAR(20),
country     VARCHAR(20),

PRIMARY KEY  (patientID),
FOREIGN KEY  (healthInsID) REFERENCES HEALTH_INSURANCES
ON DELETE SET NULL ON UPDATE CASCADE
);

I have no idea why I keep getting this error. I have search a lot but still nothing that can solve it. Any ideas?

Thanks

Upvotes: 0

Views: 660

Answers (1)

Robert
Robert

Reputation: 25753

You have to specify column from HEALTH_INSURANCES table in FOREIGN KEY caluse. You should also remove ON UPDATE CASCADE. You can use a trigger on update instead of this clause.

CREATE TABLE PATIENTS
(
patientID   NUMBER NOT NULL,
healthInsID NUMBER,
fname       VARCHAR(20) NOT NULL,
minit       VARCHAR(15),
lname       VARCHAR(30) NOT NULL,
gender      CHAR(2),
email       VARCHAR(40),
street      VARCHAR(40),
postalCode  NUMBER,
city        VARCHAR(20),
country     VARCHAR(20),

PRIMARY KEY  (patientID),
FOREIGN KEY  (healthInsID) REFERENCES HEALTH_INSURANCES (healthInsID)
ON DELETE SET NULL 
);

More information about constraints

Upvotes: 1

Related Questions