Re: Repost - Constraint - Referencing

Aftikhar Aslam <aftikhar_at_redbird.mtc.ti.com> wrote:

>SQL> alter table aa add primary key(alpha,beta,theta);
>
>Table altered.
 

>SQL> create table b
> ( gamma number(10) constraint gamma_fk references aa(alpha))
> STORAGE (INITIAL 1M
> NEXT 500K
> MINEXTENTS 1
> MAXEXTENTS 10)
>PCTFREE 5
>PCTUSED 60
>tablespace temp;
>( gamma number(10) constraint gamma_fk references aa(alpha))
> *
>ERROR at line 2:
>ORA-02270: no matching unique or primary key for this column-list

This is because in a foreign key relation you will have to use the ENTIRE rimairy key. So you'll have to make an alpha, beta AND theta column in the b-tables and use all of them ro reference to table aa.

This is quite logical because what if you would have these two rows in a:
alpha beta theta
----- ---- -----

  1     1    1
  1     2    1

This is allowed in table a because the primary key (the combination of alpha beta anf theta) is unique for all rows. With a table b like:
alpha

The first thing is that a "master"-row in table aa has two

"detail"-rows in table b. This is fine as a relation in Oracle is
either 1-1 or 1-n. But the other way around it would mean that one
"detail"-row in b would have to "master"-rows in table aa. 

This looks like a n-n relation. This is not allowed in Oracle. You would have to rewrite this:

• --- | aa |>----<| b |
• ---

Will be (to be able to use with Oracle):

• ------ --- | aa |----<| aa_b |>----| b |
• ------ ---

AA (alpha, beta, theta, some_other_column_aa) AA_B (aa_alpha, aa_beta, aa_theta, b_gamma) B (gamma, some_other_column_b)

Bye,
Wilfred
The Netherlands Received on Sat Dec 09 1995 - 00:00:00 CET