Re: Constraint problem
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