Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Post Unanswered: Foreign Key to an inherited table error

    I have a table called Person that has a primary key personID. Another table called ProUser that inherits from Person. In another table called ProPriceSchedule I have a foreign key constraint to the ProUser table, like so: CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES ProUser(personID).

    The problem I am having is this: ERROR: UNIQUE constraint matching given keys for referenced table "prouser" not found

    Here are the tables for reference:

    CREATE SEQUENCE pro_price_schedule_id;

    CREATE TABLE ProPriceSchedule
    (
    scheduleID int8 PRIMARY KEY DEFAULT NEXTVAL('pro_price_schedule_id'),
    personID int8 NOT NULL,
    title varchar (32) NOT NULL CHECK (title <> ''),
    CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES ProUser(personID)
    );

    CREATE SEQUENCE user_id;

    CREATE TABLE Person
    (
    personID int8 PRIMARY KEY DEFAULT NEXTVAL('user_id'),
    firstName varchar (32) NOT NULL CHECK (firstName <> ''),
    lastName varchar (32) NOT NULL CHECK (lastName <> ''),
    address1 varchar (64) NOT NULL CHECK (address1 <> ''),
    address2 varchar (64) ,
    suburb varchar (32) NOT NULL,
    stateID int2 NOT NULL,
    postcode varchar (8) NOT NULL,
    phone varchar (16) ,
    mobile varchar (16) ,
    email varchar (128) NOT NULL UNIQUE,
    password varchar (16) NOT NULL,
    CONSTRAINT state FOREIGN KEY (stateID) REFERENCES State(stateID)
    );

    CREATE TABLE ProUser
    (
    businessName varchar (64) NOT NULL CHECK (businessName <> ''),
    abn varchar (16) NOT NULL CHECK (abn <> ''),
    homepageHTML varchar (4096) ,
    creditLimit decimal (10, 2) ,
    enforceLimit bool NOT NULL,
    fax varchar (16)
    ) INHERITS (Person);

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    maybe

    CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES ProUser(personID)
    );

    I can see no personID in ProUser!!!!!!!!!
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Re: maybe

    Originally posted by eperich
    CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES ProUser(personID)
    );

    I can see no personID in ProUser!!!!!!!!!
    Shouldn't it be inherited from the Person Table?

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    inheritance

    Oh I have not seen the inherit
    yes should be but try to place the reference to the other table.
    maybe the foreign reference does not work on inherited tables.
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

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