Oracle循环外键引用问题 [英] Oracle cyclic foreign key references issues

问题描述

对于一个数据库类，我需要实现以下内容：

（Name Varchar2（10））（Wife Varchar2（10））
Table WIVES：（Name Varchar2（10））（Wife Varchar2（10））

  ））（丈夫Varchar2（10））
  

并使用Oracle约束，请遵循以下规则：没有两个丈夫可以有相同的名字


$ b

1. 没有两个妻子可以有相同的名字
>

2. 每个妻子必须只有一个丈夫


3. 每个丈夫必须只有一个妻子

到目前为止，我已经在Oracle SQL中实现了这个表：

<$ （
name varchar2（10）not null
，wife varchar2（10）not null
）;
创建表妻子（
name varchar2（10）not null
，husband varchar2（10）not null
）;

我很确定我已经使用正确的主键解决了点1和2：

  alter table husbands 
 add constraint husbands_pk 
主键（名称）; 
 alter table wives 
 add constraint wives_pk 
主键（名称）; 
  

这里是我遇到的问题。我想使用外键来实现步骤3和步骤4：

pre code alter table husbands
add constraint husbands_fk_wife
外键（妻子）
引用妻子（姓名）;
alter table wives
add constraint wives_fk_husband
外键（丈夫）
引用丈夫（姓名）;

现在我的教授正在使用的测试用例是能够将已婚夫妇添加到数据库中。我遇到的问题是如何使用唯一的约束来做到这一点。如果我想把杰克和吉尔添加为已婚夫妇，那么在妻子加入之前，不能添加丈夫。妻子不能添加，直到丈夫被添加。

我认为我的问题是使用外键。检查约束可能在这种情况下工作，但我不能概念化如何工作。
解决方案

需要使用可延迟的约束是往往是设计问题的指针。当然，这个数据模型并不好：它没有正确的标准化。规范化的解决方案如下所示：

  PERSON 
 ------ 
 ID号
名称varchar2（30）
 PRIMARY KEY（ID）
 
 
 MARRIED_COUPLE 
 -------------- 
 $ PARTNER_1 number 
 PARTNER_2 number 
 PRIMARY KEY（PARTNER_1，PARTNER_2）
 FOREIGN KEY（PARTNER_1）REFERENCES（PERSON.ID）
 FOREIGN KEY（PARTNER_2）REFERENCES（PERSON.ID ）
  

这有附加的优势，支持民事伙伴关系:)如果你想劝阻重婚，那么你可以将唯一的密钥放在PARTNER_1或PARTNER_2上。

模拟一夫多妻或多妻制是允许的文化是更复杂的。
$ b

edit

大卫反对的是：

  SQL>创建表married_couple（partner_1号，partner_2号）
 2 / 
 
创建表。 
 
 SQL> alter table married_couple添加主键（partner_1，partner_2）
 2 / 
 
表已更改。 
 
 SQL> insert into married_couple values（1，2）
 2 / 
 
 1 row created。 
 
 SQL> insert into married_couple values（2,1）
 2 / 
 
创建1行。 
 
 SQL> 
  

这是一个有效的点，但它是可以解决的。例如，在Oracle中，我可以创建一个独特的函数来强制排列的唯一性。

  SQL>从已删除的婚礼连结
 2 / 
 
删除2行删除。 
 
 SQL>在marriage_couple 
 2（最大（partner_1，partner_2），最小（partner_1，partner_2））
 3 / 
 
上创建唯一索引mc_uidx已创建索引。 
 
 SQL> insert into married_couple values（1，2）
 2 / 
 
 1 row created。 
 
 SQL> insert into married_couple values（2,1）
 2 / 
 insert到married_couple的值（2,1）
 * 
第1行的错误：
 ORA-00001：唯一约束（APC.MC_UIDX）违反
 
 
 SQL> 
  

为避免一夫多妻制，我们可以使用类似的技巧。我们不希望这样做：

  SQL> insert into married_couple values（1,3）
 2 / 
 
 1 row created。 
  

所以我们需要两个索引：

  SQL>从marriage_couple删除where partner_2 = 3; 
 
 1行删除。 
 
 SQL>在married_couple（最大（partner_1，partner_2））上创建唯一索引mc1_uidx 
 2 
 3 
 $ b $创建索引。 
 
 SQL>在marriage_couple上创建唯一索引mc2_uidx 
 2（至少（partner_1，partner_2））
 3 / 
 
创建的索引。 
 
 SQL> insert into married_couple values（3，1）
 2 / 
 insert到married_couple的值（3,1）
 * 
第1行出错：
 ORA-00001：唯一约束（APC.MC2_UIDX）违反
 
 
 SQL> 
  

对于那些认为使用实现技巧解决数据建模问题的作弊者，我恳求有罪收费，但我已经有了一个漫长而艰辛的一天。

I've been racking my brain trying to come up with a solution to this.

For a database class, I need to implement the following:

Table HUSBANDS: (Name Varchar2(10)) (Wife Varchar2(10))
Table WIVES: (Name Varchar2(10)) (Husband Varchar2(10))

and using Oracle constraints, enfore the following rules:

1. No two husbands can have the same name
2. No two wives can have the same name
3. Every wife must have one and only one husband
4. Every husband must have one and only one wife

So far, I have implemented the table in Oracle SQL:

create table husbands(
  name varchar2(10) not null
  , wife varchar2(10) not null
);
create table wives(
  name varchar2(10) not null
  , husband varchar2(10) not null
);

I'm pretty sure I have solved points 1 and 2 using correct primary keys:

alter table husbands
  add constraint husbands_pk
  primary key(name);
alter table wives
  add constraint wives_pk
  primary key(name);

And here is where I'm running into issues. I figured to use foreign keys to implement steps 3 and 4:

alter table husbands
  add constraint husbands_fk_wife
  foreign key(wife)
  references wives(name);
alter table wives
  add constraint wives_fk_husband
  foreign key(husband)
  references husbands(name);

Now the test case my professor is using is to be able to add a married couple to the database. The problem I am having is how to do this using only constraints. If I wanted to add Jack and Jill as a married couple, one cannot add the husband until the wife is added. the wife cannot be added until a husband is added.
I think my problem is using foreign keys. A check constraint might work in this situation, but I cannot conceptualize how it would work.

解决方案

The need to use deferrable constraints is often a pointer to design problems. Certainly this data model is not a good one: it is not properly normalised. A normalised solution would look like this:

PERSON
------
ID number 
NAME varchar2(30)
PRIMARY KEY (ID)


MARRIED_COUPLE
--------------
PARTNER_1 number
PARTNER_2 number
PRIMARY KEY (PARTNER_1, PARTNER_2)
FOREIGN KEY (PARTNER_1) REFERENCES (PERSON.ID)
FOREIGN KEY (PARTNER_2) REFERENCES (PERSON.ID)

This has the added advantage of supporting civil partnerships :) If you want to discourage bigamy then you could put unique keys on PARTNER_1 or PARTNER_2.

It is trickier to model cultures where polygyny or polyandry is permitted.

edit

What David is objecting to (in the comments) is this:

SQL> create table married_couple (partner_1 number, partner_2 number)
  2  /

Table created.

SQL> alter table married_couple add primary key (partner_1, partner_2)
  2  /

Table altered.

SQL> insert into married_couple values (1, 2)
  2  /

1 row created.

SQL> insert into married_couple values (2,1)
  2  /

1 row created.

SQL> 

It's a valid point but it is resolvable. For instance, with Oracle I can create a unique function-based to enforce uniqueness of permutations.

SQL> delete from married_couple
  2  /

2 rows deleted.

SQL> create unique index mc_uidx on married_couple 
  2     (greatest(partner_1, partner_2),least(partner_1, partner_2))
  3  /

Index created.

SQL> insert into married_couple values (1, 2)
  2  /

1 row created.

SQL> insert into married_couple values (2,1)
  2  /
insert into married_couple values (2,1)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.MC_UIDX) violated


SQL>

To avoid polygamy we can use a similar trick. We don't want this:

SQL> insert into married_couple values (1,3)
  2  /

1 row created.

So, we need two indexes:

SQL> delete from married_couple where partner_2 = 3;

1 row deleted.

SQL> create unique index mc1_uidx
  2      on married_couple (greatest(partner_1, partner_2))
  3  /

Index created.

SQL> create unique index mc2_uidx
  2      on married_couple (least(partner_1, partner_2))
  3  /

Index created.

SQL> insert into married_couple values (3, 1)
  2  /
insert into married_couple values (3, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.MC2_UIDX) violated


SQL>

To those who think it's cheating to solve a data modelling issue with an implementation trick, I plead "Guilty as charged" but I have had a long and trying day of it.

这篇关于Oracle循环外键引用问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！