Re: Constraint problem

From: Svenn Helge Grindhaug <svenn_at_ii.uib.no>
Date: 2000/05/24
Message-ID: <392BC0B1.C11DA29F_at_ii.uib.no>#1/1


Philip Lijnzaad wrote:

> Svenn> I have created the following tables
> Svenn> create table complex (
> Svenn> cacc# number not null primary key,
> Svenn> cname varchar2(40) not null unique,
> Svenn> pre# number,
> Svenn> );
>
> Svenn> create table c_ref (
> Svenn> cacc# number not null,
> Svenn> ref# number not null,
> Svenn> primary key (cacc#,ref#)
> Svenn> );
>
> Svenn> Then i want to add the following constraint
 

> SQL> alter table complex
> Svenn> 2 add constraint c_cacc#_fk
> Svenn> 3 foreign key (cacc#)
> Svenn> 4 references c_ref (cacc#);
>
> Svenn> But I get the error message: ORA-02270: no matching unique or primary
> Svenn> key for this column-list
> Svenn> Can anyone tell me why and how to solve this problem?
>
> The primary key of c_ref is composite (cacc#, ref#), not cacc# by itself. If
> c_ref.cacc# is not enough to be a primary key, then surely you can't make a
> referential constraint to it. Alternatively, if it would be, then why is
> (cacc#, ref#) made the primary key of c_ref? I'm sure Carsten Helgesen has
> taught you all this :-) Cheers,
>
> Philip

(I ment to alter table complex, not proteincomplex)

I have 2 entities complex and reference, and the relations between them are;

    each complex MUST have at LEAST ONE (or more) reference(s).     each reference CAN refer to at LEAST ONE (or more) complex(es).

So i created the 2 tables you have seen and

create table reference (
 ref# number not null primary key,
 depositedby varchar2(40) not null,
 change long
);

The c_ref table keeps track of which complexes has which references and vice versa. Thats why (cacc#,ref#) is unique in c_ref.

So how would I make sure that for every complex has a reference and every reference refers to a complex?

Cherrs.

Svenn. Received on Wed May 24 2000 - 00:00:00 CEST

Original text of this message