Re: Constraint problem

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> 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 primary

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-24


+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