Re: Constraint problem

From: Arto V. Viitanen <av_at_siwenna.cs.uta.fi>
Date: 2000/05/24
Message-ID: <wer9asi775.fsf_at_siwenna.cs.uta.fi>#1/1

>>>>> "Svenn" == Svenn Helge Grindhaug <svenn_at_ii.uib.no> writes:

 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

 Svenn> SQL> alter table proteincomplex
 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 Can anyone tell me why and how to solve this  Svenn> problem?

Foreign key refers to the primary key of the other relation. You try to refer to a part of the primary key, which does not hold the uniqueness (if it does, the primary key is not a key!). Either make the foreign key to be (cacc#, ref#) or use surrogate or similar keys.

-- 
Arto V. Viitanen				                  av_at_cs.uta.fi
University of Tampere, Department of Computer and Information Sciences
Tampere, Finland				      http://www.cs.uta.fi/~av/

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