sql - 生成数据到数据库

Question

我有一个关于为数据库生成数据的问题。一般来说我没有问题，但我不知道如何生成正确的外键。示例：我有三个表：Factory、Worker 和 Product。表产品有两个外键：到工厂和工人，表工人有工人的主键和工厂的外键，所以如果我首先为产品生成数据，我在工厂和工人之间有链接，那么我现在如何才能与工人约会？

标签： sqloracledata-generation

Answer 1

如果启用了外键，则必须先插入父记录，然后再插入子记录。

SQL> create table factory
  2    (id number primary key);

Table created.

SQL> create table worker
  2    (id      number primary key,
  3     id_fact number references factory
  4    );

Table created.

SQL> create table product
  2    (id      number primary key,
  3     id_fact number references factory,
  4     id_work number references worker
  5    );

Table created.

SQL>
SQL> insert into factory values (1);

1 row created.

SQL> insert into worker values (100, 1);

1 row created.

SQL> insert into product values (1000, 1, 100);

1 row created.

SQL>

你不能 - 正如你所说 -product首先插入值，因为父行还不存在：

SQL> rollback;

Rollback complete.

SQL> insert into product values (1000, 1, 100);
insert into product values (1000, 1, 100)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C007766) violated - parent key not
found


SQL>

---

如果您创建了可延迟的外键约束，那么您可以按照您想要的任何顺序插入行，因为 Oracle 将在提交时检查完整性：

SQL> create table factory
  2    (id number primary key);

Table created.

SQL> create table worker
  2    (id      number primary key,
  3     id_fact number references factory initially deferred deferrable
  4    );

Table created.

SQL> create table product
  2    (id      number primary key,
  3     id_fact number references factory initially deferred deferrable,
  4     id_work number references worker  initially deferred deferrable
  5    );

Table created.

SQL> insert into product values (1000, 1, 100);

1 row created.

SQL> insert into factory values (1);

1 row created.

SQL> insert into worker values (100, 1);

1 row created.

SQL>