使用循环引用在表中插入SQL [英] INSERT in TABLES with circular references SQL

问题描述

我有2张桌子：

Empleados(**numEmpl**, nombre, apellido, sexo, telefono, salario, numDept)
Departamentos(**numDept**, nombreDept, numDirect)

在departamentos中：

In departamentos:

1. numEmpl是主键


2. numDept是对Departamentos（numDept）的外键引用。
   在部门中：


3. numDept是主键


4. 而numDirect是对Empleados（numEmpl）的外键引用

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)

因此有一个循环引用。

首先，我创建了表格：

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);

但是现在它抛出了一个错误，告诉我我不能在循环引用中引入数据，我试图禁用循环引用并插入数据，然后再次启用它，它起作用了，但是有人告诉我这不是正确的方法，在创建表以在其中插入数据时，我必须做一些特殊的事情。这样，它会起作用，但我不知道如何去做。
顺便说一下，我正在使用oracle sql developer。

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.

编辑：感谢您的回答，但它们没有用。首先，我只能拥有该表，当我进行插入操作时，必须以这种方式工作，而无需将参数设为null并进行更新，很抱歉，我之前没有说过。
所以我要做的唯一方法就是允许圆引用，但是当我尝试以某人在这里所说的方式来做它时，它告诉我一些有关回滚的信息，有人可以提供帮助吗？

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?

推荐答案

要允许循环引用，您需要可延迟的约束：

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
    ;

在交易结束检查可延迟约束；在提交时间之前，允许存在虚假的无效的数据库状态（在原始问题中：两个插入语句之间）。但是语句必须在事务内部，因此语句应包含在 BEGIN [WORK]; 和 COMMIT [WORK]; 。

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

这篇关于使用循环引用在表中插入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！