INSERT problem HELP

[pradeepbha]

I have the following 2 tables

SQL> desc EMPLOYEE
Name Null? Type
----------------------------------------------------- -------- ------------------------------
FNAME NOT NULL VARCHAR2(15)
LNAME NOT NULL VARCHAR2(15)
SSN nOT NULL CHAR(9)
BDATE DATE
ADDRESS VARCHAR2(30)
SEX CHAR(1)
SALARY NUMBER(10,2)
SUPERSSN CHAR(9)
DNO NOT NULL NUMBER


and

SQL> desc DEPARTMENT
Name Null? Type
----------------------------------------------------- -------- ------------
DNUMBER NOT NULL NUMBER
DNAME NOT NULL VARCHAR2(15)
MGRSSN NOT NULL CHAR(9)
MGRSTARTDATE DATE


The DNo column in employee , a foreign key refers to DNUMBER in Department ... and MGRSSN in Departement is a foreign key referring to SSN of Employee. Both the tables are empty. Now, how do i insert values into the 2 tables. Is it possible?

[jmodic]

You would have to make at least one of those FK constraints deferrable. Then you would set that constraint temporarily as deffered (SET CONSTRAINT my_fk_constraint DEFERRED), insert record into the table with deffered constraint first, insert record in the other table next, and then make the constraint non-deferred agin (SET CONSTRAINT my_fk-constraint IMMEDIATE).

Athough I would say that this kind of design is a bit strange....

[pradeepbha]

hI,
I AM NOT ABLE TO DEFER THE CONSTRAINT

SQL> SET CONSTRAINT MGRSSN_CONS DEFERRED
2 ;
set CONSTRAINT MGRSSN_CONS DEFERRED
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

SQL> SET CONSTRAINT DNO_CONS DEFERRED
2 ;
set CONSTRAINT DNO_CONS DEFERRED
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

CAN SOME ONE PLZ HELP ME OUT.
THANX IN ADV
---------

Originally posted by jmodic
You would have to make at least one of those FK constraints deferrable. Then you would set that constraint temporarily as deffered (SET CONSTRAINT my_fk_constraint DEFERRED), insert record into the table with deffered constraint first, insert record in the other table next, and then make the constraint non-deferred agin (SET CONSTRAINT my_fk-constraint IMMEDIATE).

Athough I would say that this kind of design is a bit strange....

[jmodic]

You must drop and recreate those constraints (at least one of them) and make them DEFERRABLE before you can actually defer their constraint checking. Check the SQL manual for the exact syntax.