Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

  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?

like image 964
carlos Avatar asked Apr 14 '13 14:04

carlos


People also ask

What is a circular reference SQL?

What is a Circular Reference? Foreign keys create database-enforced integrity constraints. These constraints ensure that a row of data exists in one table before another table can reference it. They also prevent a dependent row from being deleted that another row references.

What is circular dependency in SQL?

Circular Dependency : Here when we try to delete a record from TABLE A, it throws an error message as Column P & Column R of TABLE B are depending on Column Q of TABLE A. When we try to delete a record from TABLE B, it again throws an error message as Column P of TABLE A is depending on Column P of TABLE B.

Are circular references OK?

Circular Reference means that your formula is trying to calculate the origin cell. Typically, this is considered an error. However, there are times where this error can actually be useful and you might to want to create a circular reference on purpose.

Should circular references avoid?

However, as a general practice, there are several reasons why you may want to avoid circular references between objects. Data and graph consistency. Updating objects with circular references can create challenges in ensuring that at all points in time the relationships between objects are valid.


1 Answers

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

like image 75
wildplasser Avatar answered Sep 30 '22 10:09

wildplasser