Syntax Error for "ON UPDATE CASCADE ON DELETE CASCADE"

Question

Sorry for asking such a dumb question but I am trying to make two tables: parent and child. The parent has multiple other children as well, and all of them seem to have this error. Can anyone please tell me why its saying that? I am using Sql.

I have tried rewriting the whole table, and tried using only on delete or update cascade separately as well but its not working. I tried researching other similar questions but none of them is telling me the reason for this.

The parent table; already created, no problems with it.

create table Person(
SSN int,
FName Varchar(30),
LName Varchar(30) Not Null,
Address Varchar(30) Not Null,
Age int Not Null,
Gender VarChar(20) not Null,
Phone Varchar(200) Not Null,
Primary key(SSN),
Check (Age >0)
);

And this (doctor) is the child table. When i try to run the query, it says I am missing a key word. But what word?

create table Doctor(
SSN int not Null,
Doc_id int not Null,
Dept_id int not Null,
HireDate Date not Null,
foreign key(SSN) references Person(SSN)
on update cascade on delete cascade
);

Following is another child class (just in case someone wants to see it)

create table Patient(
SSN int,
Pat_id int,
RegDate date not Null,
foreign key(SSN) references Person(SSN)
ON UPDATE CASCADE ON DELETE CASCADE
);

I also tried to remove the comma after that RegDate statement but then it says I am missing a right paranthesis.

When I remove the comma:

Error report - ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action:

With Comma:

Error report - ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause:
*Action:

What should I do?

Don't use SSN as a key. Don't even store it without encrypting it. If you do this in The Real World you will cause yourself and whatever company you're working for no end of grief, especially if you happen to be in the retail or related sectors where you may have data on customers. You will find out exactly how bad things can get the day after Russian/Chinese/teen-age-mutant-ninja-turtle hackers bust into your data store and make off with whatever unencrypted customer PII (Peronally Identifiable Information) they can get their hands on. — Bob Jarvis - Слава Україні, Jun 09 '19 at 22:22

score 1 · Accepted Answer · answered Jun 09 '19 at 21:50

Oracle does not support cascades on updates, only deletes. So this works fine:

create table Doctor(
    SSN int not Null,
    Doc_id int not Null,
    Dept_id int not Null,
    HireDate Date not Null,
    foreign key(SSN) references Person(SSN) on delete cascade
);

See here.

This is generally not a big limitation because you shouldn't be changing primary key values anyway.

Also, don't store social security numbers as integers. They are really strings that happen to be digits. In the US, at least, they can have leading zeros.

I see. That explains it. Thanks alot. No wonder the update cascade was causing problem. It works now. I also fixed the SSN thing, thank you again. — Freed, Jun 09 '19 at 22:10

Syntax Error for "ON UPDATE CASCADE ON DELETE CASCADE"

1 Answers1