integrity constraints problem

[Adrock952]

I am trying to create my tables where if i delete/update a record from one table, all the other tables are affected by deleting/updating any records that reference the original record.

For example, if i delete/update a record from the employee table, the other tables that are referenced to it will be updated/deleted

Here is the database create SQL i have

CREATE TABLE Employee (
  ssn             NUMBER (4) PRIMARY KEY,
  salary          NUMBER (5),
  phone          NUMBER (11)
  );
 
CREATE TABLE Department (
  dno          NUMBER (1) PRIMARY KEY,
  dname          VARCHAR2 (20),
  budget      NUMBER (6)
  );
 
CREATE TABLE Child (
  name            VARCHAR2 (20) UNIQUE,
  age          NUMBER (2),
  check(age BETWEEN 0 AND 18)
  );    
 
CREATE TABLE Children (
  ssn          NUMBER (4),
  name          VARCHAR2 (20),
  CONSTRAINT emp_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT chd_FK FOREIGN KEY(name) references Child(name) ON DELETE SET NULL ON UPDATE CASCADE
  );
 
CREATE TABLE Works (
  ssn          NUMBER (4),
  dno          NUMBER (1),
  CONSTRAINT empl_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE, 
  CONSTRAINT dept_FK FOREIGN KEY(dno) references Department(dno) ON DELETE SET NULL ON UPDATE CASCADE 
  );
 
CREATE TABLE Manages (
  ssn          NUMBER (4),
  dno          NUMBER (1),
  CONSTRAINT employ_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE, 
  CONSTRAINT depart_FK FOREIGN KEY(dno) references Department(dno) ON DELETE SET NULL ON UPDATE CASCADE 
  );
 
CREATE TABLE NewMember (
  ssn          NUMBER (4),
  dno           NUMBER (1)
  );

When i try to run the code I get this error

ORA-00907: missing right parenthesis

and it points to these lines

CONSTRAINT emp_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT empl_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE
CONSTRAINT employ_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE

It seems strange that the other foreign keys aren't affected and i don't know if they will when i fix this problem

[amitpatel66]

Try this:

CREATE TABLE Children (
  ssn          NUMBER (4),
  name          VARCHAR2 (20),
  CONSTRAINT emp_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL,
  CONSTRAINT chd_FK FOREIGN KEY(name) references Child(name) ON DELETE SET NULL 
  );
 

Try the same for all other CREATE table statements as well

[Adrock952]

I figured it out last night. It turns out that Oracle doesn't have an ON UPDATE constraint so i deleted that and it worked

[amitpatel66]

I figured it out last night. It turns out that Oracle doesn't have an ON UPDATE constraint so i deleted that and it worked

Yes thats right. but you can achieve this by creating user defined procedures and a trigger to achieve this functionality!!

[rallen]

Yes thats right. but you can achieve this by creating user defined procedures and a trigger to achieve this functionality!!

You can also use dbConstructor to create your scripts and it will automatically generate the declarative and trigger referential constrain.

Regards,

Robert Allen Schambach