Re: Constraint problem
From: Philip Lijnzaad <lijnzaad_at_ebi.ac.uk>
Date: 2000/05/24
Message-ID: <u7aehg1dqm.fsf_at_o2-3.ebi.ac.uk>#1/1
Svenn> primary key (cacc#,ref#)
Svenn> );
Svenn> Can anyone tell me why and how to solve this problem?
Date: 2000/05/24
Message-ID: <u7aehg1dqm.fsf_at_o2-3.ebi.ac.uk>#1/1
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 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 primarySvenn> 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
-- /dev/brain: character special (53/0) ----------------------------------------------------------------------------- Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-24Received on Wed May 24 2000 - 00:00:00 CEST
+44 (0)1223 49 4639 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax) \ Cambridgeshire CB10 1SD, GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC 50 3D 1F 64 40 75 FB 53