Geek asks and answers

Oracle cyclic foreign key issues

I am trying to find a solution for this.

For the 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 restrictions, with the following exceptions:

  • No two husbands having the same name
  • No two wives can have the same name.
  • Each wife must have one and only one husband
  • Every husband should have one and only wife

So far I have implemented a 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 am sure that I solved points 1 and 2 using the 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 I ran into problems. I decided 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);

, , - . , , , , . , , ​​. ​​ , .
, . , , .

0
8

(, ) ( , ) . / / .

PS. , ?

+3

. , : . :

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)

:) , PARTNER_1 PARTNER_2.

, .

, ( ), :

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>

, . , Oracle , .

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>

, . :

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

1 row created.

, :

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>

, , , ", ", .

+3

( , ), .

+2

- . , - Oracle 10gR2:

SQL> CREATE OR REPLACE TRIGGER husband_wife_trg AFTER INSERT ON husbands
  2  FOR EACH ROW
  3  BEGIN
  4     INSERT INTO wives VALUES (:new.wife, :new.name);
  5  END;
  6  /

Trigger created

SQL> INSERT INTO husbands VALUES ('Husband A', 'Wife B');

1 row inserted

SQL> SELECT * FROM wives;

NAME       HUSBAND
---------- ----------
Wife B     Husband A

, , .

+1

- "" "Husband_Name" "Wife_Name", ? , .:)

+1

1) setAutoCommit() false 2) . 3)

0

, /, 40 .

0

- :

" "

: !!! * !

, :

  • : , - , , ( , - ... !!!)

  • INSERT ALL - , Oracle Versions INSERT ALL, . , "Insert wife and hsuband", .

  • : Trigger - , ...

But all the other answers were simply incorrect for the proposed problem: two objects with a mandatory connection of 1 to 1

0
source

More articles:


All Articles