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?

Answer 1

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.