Re: Problem with foreign keys - Mailing list pgsql-general

hi,
you can not reference to a part of the multicolumn primery(unique) key.
As you can see the table MachineTypehas a joined primery key (machinetypeid,
familyid). so if you want to refrence to this table in order to point to
maximum of one row you should provide the familyid too.
regards
Omid
>
>create sequence customer_customerid_seq;
>
>create table customer (
>         customerid integer,
>         companyname varchar,
>         attention varchar,
>         address1 varchar not null,
>         address2 varchar,
>         zip varchar not null,
>         city varchar not null,
>         country varchar,
>         phone varchar not null,
>         fax varchar,
>         email varchar,
>         website varchar,
>         created timestamp with time zone default now() not null,
>         active boolean default't'::bool not null,
>         comment varchar,
>         primary key (customerid)
>);
>
>create sequence componentid_seq;
>
>create table component (
>         componentid int4 not null,
>         parttype varchar not null,
>         brand varchar not null,
>         partnumber varchar not null,
>         description varchar,
>         producturl varchar,
>         supporturl varchar,
>         driverurl varchar,
>         primary key(componentid)
>);
>
>create sequence basetypeid_seq;
>
>create table basetype(
>         basetypeid int4 not null,
>         name varchar not null,
>         primary key (basetypeid)
>);
>
>create sequence familyid_seq;
>
>create table family (
>         familyid int4 not null,
>         familyname varchar not null,
>         basetypeid int4 not null references basetype(basetypeid),
>         primary key(familyid)
>);
>
>create sequence machinetypeid_seq;
>
>create table machinetype(
>         machinetypeid int4 not null,
>         familyid int4 not null references family(familyid),
>         partname varchar not null,
>         price float,
>         deliverystatus varchar,
>         primary key(machinetypeid, familyid)
>);
>
>create table rel_type_component (
>         componentid int4 not null references component(componentid),
>         machinetypeid int4 not null references
>machinetype(machinetypeid),
>         primary key (componentid, machinetypeid)
>);
>
>### ERROR: ERROR:  UNIQUE constraint matching given keys for
>referenced table "machinetype" not found
>
>create sequence machineid_seq;
>
>create table machine(
>         machineid int4 not null,
>         customerid int4 not null references customer(customerid),
>         machinetypeid int4 not null references
>machinetype(machinetypeid),
>         bought timestamp,
>         primary key (machineid)
>);
>
>### ERROR: ERROR:  UNIQUE constraint matching given keys for
>referenced table "machinetype" not found
>
>create sequence serviceid_seq;
>
>create table servicerecord(
>         serviceid int4 not null,
>         machineid int4 not null references machine(machineid),
>         opened timestamp not null default now(),
>         closed timestamp,
>         description varchar not null,
>         primary key(serviceid)
>);
>
>### this is understandable, as prior creation failed. :)
>### ERROR:  Relation 'machine' does not exist
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp