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

Answer 2

Circular references are dangerous, and cause you to need to go back and update your data so it is not in an inconsistent state.

If you are in your planning stages still I urge you to take a look at other options to avoid this, otherwise you may run into a lot of headaches down the road.

http://blogs.msdn.com/b/sqlazure/archive/2010/07/01/10033575.aspx

If you do wish to use them still, then I would suggest setting NULL as an allowed value on the departments table (this allows you to insert a new value with no d), Inserting the employee, and then go back and update with the employee id.

Answer 3

This is happening because you can't create a record in the Department table with a value of 1 for numDirect until you have created a record in the Employees table for that employee (numEmpl=1). And you can't create the employee until you have created his department record. This is solved by making the process three steps instead of just two. To do this you have to be able to create the Department record without the numDirect FK value, or you have to able to create the Employee without the numDept FK value.

Say you decide on the latter. In that case Make NumDept Nullable in table EMPLEADOS :

Alter table EMPLEADOS Alter Column numDept null

Then you can:
First, Add a employee with a null value for numDept

INSERT Empleados(numEmpl, nombre, apellidos,
        sexo, telefono, salario, numDept)
VALUES (1, 'Pepito', 'Pérez', 'H', '111111111', 20000, null);

Second, Add employee:

INSERT Departamentos(numDept, nombreDept, numDirect)
VALUES (1, 'Direccion', 1);

And finally, Update value of numDept in Department record.

Update Empleados Set numDept = 1 
Where numEmpl = 1

Answer 4

Take the numDirect column out of your departamentos table. That table should simply describe the department. Depending on your business rules, you want a one to many relationship between departamentos and Empleados, which you have, or a many to many relationship between them. If an Empleado can work for more than one departamento, then you want to drop the numDept column from the Empleados table and create another table to set up the many to many relationship.

If you manage to figure out a way to add records with your current design, you will have a bigger problem. Instead of having just one record for each departamento, you will need one for every Empleado in it.