Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Question Unanswered: Multiple FK from same table give error

    I am trying to create a table that has two columns which are keys in another table. I have provided a sample of the two tables below. When I try and create the tables under postgreSQL 7.4 I get the following error.

    ERROR: there is no unique constraint matching given keys for referenced table "order_header"

    Here are the two tables.

    create table order_header (
    ord_nbr char(10),
    ord_sfx char(3),
    cust_nbr char(10),
    primary key(ord_nbr,ord_sfx));


    create table order_detail (
    line_nbr numeric(3,0),
    ord_nbr char(10) references order_headert(ord_nbr),
    ord_sfx char(3) references order_header(ord_sfx),
    item_nbr char(10),
    Item_qty numeric(5,0),
    primary key(ord_nbr, ord_sfx, line_nbr));

    Can anyone explain to me what the problem is and how to correct it?
    Last edited by pgsql_how; 07-27-04 at 10:23.

  2. #2
    Join Date
    Jun 2004
    Posts
    31
    Quote Originally Posted by pgsql_how
    I am trying to create a table that has two columns which are keys in another table. I have provided a sample of the two tables below. When I try and create the tables under postgreSQL 7.4 I get the following error.

    ERROR: there is no unique constraint matching given keys for referenced table "order_header"

    Here are the two tables.

    create table order_header (
    ord_nbr char(10),
    ord_sfx char(3),
    cust_nbr char(10),
    primary key(ord_nbr,ord_sfx));


    create table order_detail (
    line_nbr numeric(3,0),
    ord_nbr char(10) references order_headert(ord_nbr),
    ord_sfx char(3) references order_header(ord_sfx),
    item_nbr char(10),
    Item_qty numeric(5,0),
    primary key(ord_nbr, ord_sfx, line_nbr));

    Can anyone explain to me what the problem is and how to correct it?
    I suspect that the problem is that you are specifying two distinct foreign keys where you want one.

    Try:

    create table order_detail (
    line_nbr numeric(3,0),
    ord_nbr char(10),
    ord_sfx char(3),
    item_nbr char(10),
    Item_qty numeric(5,0),
    FOREIGN KEY (ord_nbr, ord_sfx) REFERENCES order_headert,
    primary key(ord_nbr, ord_sfx, line_nbr));

  3. #3
    Join Date
    Jul 2004
    Posts
    2

    Thumbs up

    That was just the answer I needed. Thanks for the tip.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •