INSERT in TABLES with circular references SQL

circular-reference, create-table, sql, sql-insert

Solution

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];`.

Problem

I have 2 tables: ``` Empleados(**numEmpl**, nombre, apellido, sexo, telefono, salario, numDept) Departamentos(**numDept**, nombreDept, numDirect) ``` In departamentos: - numEmpl is primary key - numDept is foreign key reference to Departamentos(numDept). In departamentos: - numDept is the primary key - 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?

Original source