Urgent!! - Deferred constraints

From: Evelyn <onezero_at_post1.com>
Date: Mon, 11 Oct 1999 00:06:26 +1000
Message-ID: <38009c8e_2@news.ausmail.com>

Hi, thank you for taking your time to read my posting. i have these 2 tables that i have problem with.

Employee {ssn, name, bdate, address, superssn, dno}PK=ssn, FK=dno, Department {dname, dnumber, mgrssn}PK=dnumber, FK=mgrssn where dno = dnumber = department number

the problem is, i can't reference a column of a table that has not yet been defined. An EMPLOYEE must work for a DEPARTMENT. A DEPARTMENT must have a manager who is an EMPLOYEE.

One solution is to create the DEPARTMENT table first without referencing the EMPLOYEE table. Then, create the EMPLOYEE table and have the DNO attribute reference DEPARTMENT(DNUMBER). After both tables have been successfully created, use the following ALTER command to add the constraint:

     alter table DEPARTMENT add (
            constraint mgrssn_fk foreign key (mgrssn) references

EMPLOYEE(ssn) initially deferred deferrable );

however, i always have problem with this alter statement, it will return an error message saying that "right bracket expected".. if i removed the "initially deferred deferrable", everything is fine..

any suggestion what's goes wrong?? Thanks for your time.

--
Cheers
Evelyn Received on Sun Oct 10 1999 - 09:06:26 CDT