Jump to content

[SOLVED] Using constraints


AdRock

Recommended Posts

I have some tables and i am trying to add some constraints so when you delete from one table, it deletes all records that are in other tables that reference it

 

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),
  PRIMARY KEY (ssn, name),
  CONSTRAINT parent FOREIGN KEY(ssn) references Employee(ssn) ON DELETE CASCADE CONSTRAINT parent ON UPDATE CASCADE CONSTRAINT CONSTRAINT parent,
  CONSTRAINT child FOREIGN KEY(name) references Child(name) ON DELETE CASCADE CONSTRAINT child ON UPDATE CASCADE CONSTRAINT child
  );

CREATE TABLE Works (
  ssn		  NUMBER (4),
  dno		  NUMBER (1),
  PRIMARY KEY (ssn, dno),
  CONSTRAINT employee FOREIGN KEY(ssn) references Employee(ssn) ON DELETE CASCADE CONSTRAINT employee ON UPDATE CASCADE CONSTRAINT employee, 
  CONSTRAINT department FOREIGN KEY(dno) references Department(dno) ON DELETE CASCADE CONSTRAINT department ON UPDATE CASCADE CONSTRAINT department
  );

CREATE TABLE Manages (
  ssn		  NUMBER (4),
  dno		  NUMBER (1),
  PRIMARY KEY (ssn, dno),
  CONSTRAINT supervisor FOREIGN KEY(ssn) references Employee(ssn) ON DELETE CASCADE CONSTRAINT supervisor ON UPDATE CASCADE CONSTRAINT supervisor, 
  CONSTRAINT depart FOREIGN KEY(dno) references Department(dno) ON DELETE CASCADE CONSTRAINT depart ON UPDATE CASCADE CONSTRAINT depart
  );

CREATE TABLE NewMember (
  ssn		  NUMBER (4),
  dno 		  NUMBER (1)
  );

 

What am i doing wrong with these constraints?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.