I have 2 tables:
Empleados(**numEmpl**, nombre, apellido, sexo, telefono, salario, numDept)
Departamentos(**numDept**, nombreDept, numDirect)
In departamentos:
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?
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.
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.
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.
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.
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];
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With