Insert In Tables With Circular References Sql

I have 2 tables: Empleados(**numEmpl**, nombre, apellido, sexo, telefono, salario, numDept) Departamentos(**numDept**, nombreDept, numDirect) In departamentos: numEmpl is primary

Solution 1:

To allow cyclic references, you need deferrable constraints:

ALTERTABLE DEPARTAMENTOS 
    ADDCONSTRAINT FK_DEPT_EMP FOREIGN KEY (numDirect) 
    REFERENCES EMPLEADOS(numEmpl)
    DEFERRABLE INITIALLY DEFERRED
    ;
ALTERTABLE EMPLEADOS 
    ADDCONSTRAINT 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];.

Solution 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.

Solution 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 :

Altertable EMPLEADOS AlterColumn 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 =1Where numEmpl =1

Solution 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.