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:
[code=oracle]
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
);
[/code]

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