Re: Non-primary key constraints in Oracle

A foreign key constraint must reference a primary/unique key in the referenced table.
Otherwise, you get
ORA-02270: no matching unique or primary key for this column-list "Model" is not unique in table "Car", so in fact you cannot create a foreign key. (The reason is performance, I believe:
You could create an index on "model" in table "car", but the RDBMS might not be handle the duplicates that efficient.)

Solution (Workaround?) 1:
Create a 3rd table "Car_model" and let tables "Car" and "Customer" reference them:

• indexes omitted create table Car_model (model varchar2(20) CONSTRAINT unq_model UNIQUE); alter table customer add foreign key (model) references Car_model(model); alter table car add foreign key (model) references Car_model(model);

SQL> insert into car_model values('GX2'); 1 row created.
SQL> insert into car values ('GX2', '2-door'); 1 row created.
SQL> insert into car values ('GX2', '4-door'); 1 row created.
SQL> insert into customer values ('Joe', 'GX2'); 1 row created.
SQL> insert into customer values ('Bob', 'GX1'); ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0011011) violated - parent key not found

Solution 2:
Create a trigger:

create or replace trigger t1 before delete or insert or update on customer for each row
declare m car.model%type;
begin
  select model into m from car where model=:new.model; exception
  WHEN TOO_MANY_ROWS THEN
    NULL;
end;
/

SQL> insert into customer values ('Bob', 'GX1'); ERROR at line 1:

ORA-01403: no data found
ORA-06512: at "SCOTT.T1", line 3
ORA-04088: error during execution of trigger 'SCOTT.T1'

Hope this helps.
Rainer

ted_graham_at_mail.amsinc.com wrote:

> We want to have Oracle inforce an integrity constraint between two tables,
> where the column that we want to inforce is only part of the primary key
> on the other table.
>
> For example,
> imagine a Car table, with a primary key of two columns: Model and Type.
>
> Model Type
> ------------------
> GX2 2-door
> GX2 4-door
>
> Then, in the Customer table, we want to track the Model of car that the
> customer has purchased. We want to make sure that the value in the Customer's
> Model column is valid (by checking it against Car:Model), but we don't care
> what Type of car the customer has.
>
> Name Model
> -------------------
> Joe GX2 <---------Fine
> Bob GX1 <-------We want Oracle to catch this
Received on Tue Jun 23 1998 - 11:50:26 CDT