INSERT in TABLES with circular references SQL

Question

I have 2 tables:

Empleados(**numEmpl**, nombre, apellido, sexo, telefono, salario, numDept)
Departamentos(**numDept**, nombreDept, numDirect)

In departamentos:

1. numEmpl is primary key
2. numDept is foreign key reference to Departamentos(numDept). In departamentos:
3. numDept is the primary key
4. And numDirect is foreign key reference to Empleados (numEmpl)

So there is a circular reference.

First of all I created the Tables:

CREATE TABLE EMPLEADOS(numEmpl primary key, nombre, 
     apellido, sexo, telefono, salario, numDept)
CREATE TABLE DEPARTAMENTOS(numDept primary key, nombreDept, numDirect)
(i didn't write here each of type is each colum)

Now I create the reference between them:

ALTER TABLE DEPARTAMENTOS 
    ADD CONSTRAINT FK_DEPT_EMP FOREIGN KEY (numDirect) 
    REFERENCES EMPLEADOS(numEmpl)
ALTER TABLE EMPLEADOS 
    ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY (numDept) 
    REFERENCES DEPARTAMENTOS(numDept).

It worked, so now I tried to insert some data:

INSERT INTO Empleados(numEmpl, nombre, apellidos, sexo, telefono, salario, numDept)
VALUES (1, 'Pepito', 'Pérez', 'H', '111111111', 20000, 1);
INSERT INTO Departamentos(numDept, nombreDept, numDirect)
VALUES (1, 'Direccion', 1);

But now it throws me an error, telling me I can't introduce data in a circular reference, I tryed to disable the circular reference and insert the data, and then enable it again, it worked but someone told me it isn't the right way and I have to do something special while I'm creating the tables to insert the datas in that way and it will work, but I don't have idea how to do it. I'm using oracle sql developer by the way.

EDIT: Thanks for the answers, but they didn't worked. First of all I only can have that tables, and when I make the insert it MUST work in that way, without making a parameter null and then update it, I'm sorry I didn't say it before. So the only way I have to do it, it's allowing the circle reference, but when I try to do it in the way someone said here, it tells me something about a rollback, someone can help?

Answer 1

To allow cyclic references, you need deferrable constraints:

ALTER TABLE DEPARTAMENTOS 
    ADD CONSTRAINT FK_DEPT_EMP FOREIGN KEY (numDirect) 
    REFERENCES EMPLEADOS(numEmpl)
    DEFERRABLE INITIALLY DEFERRED
    ;
ALTER TABLE EMPLEADOS 
    ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY (numDept) 
    REFERENCES DEPARTAMENTOS(numDept)
    DEFERRABLE INITIALLY DEFERRED
    ;

Deferrable constraints are checked at transaction end; before commit time a spurious invalid database state is allowed to exist (in the original question: between the two insert statements). But the statements must be inside a transaction, so the statements should be enclosed in BEGIN [WORK]; and COMMIT [WORK];.