AdRock Posted November 23, 2007 Share Posted November 23, 2007 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.