Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Question Unanswered: Table inheritance and foreign key...

    Try to run the folllowing code:
    Code:
    CREATE TABLE "Object"
    (
        "ObjectID" BIGINT NOT NULL,
        CONSTRAINT "PK_Object" PRIMARY KEY ("ObjectID")
    );
    
    CREATE TABLE "A"
    (
        "Ref_Object" BIGINT,
        CONSTRAINT "PK_A" PRIMARY KEY ("ObjectID"),
        CONSTRAINT "FK_A_to_Object" FOREIGN KEY ("Ref_Object") REFERENCES "Object"("ObjectID")
    ) INHERITS("Object");
    
    INSERT INTO "Object" VALUES (1), (2);
    
    INSERT INTO "A" VALUES (3,1);
    
    --the next row causes an error
    INSERT INTO "A" VALUES (4,3);
    
    --ERROR:  insert or update on table "A" violates foreign key constraint "FK_A_to_Object"
    --DETAIL:  Key (Ref_Object)=(3) is not present in table "Object".
    I tried to do a SELECT FROM "Object" and the value 3 is there. But whe doing EXPLAIN on that you can see that it merges "PK_Object" and "PK_A" before doing the search. But it seems that the FOREIGN KEY constraint does not do that. Can anybody help me solve this?

  2. #2
    Join Date
    Mar 2008
    Posts
    14
    Hey,

    you need to know that PostgreSQL create a query plan based on various variables, one of them is the statistical size of each table.

    So when you run the above SQL and you only insert one record,
    it might just well be that PostgreSQL create a query plan that is totally different then when you load each table up with a sensible a mouth of records. Often you will see that when tables are really small that PostgreSQL will not use an index at all but simple will issue a table scan rather then a index scan.

    Ries

  3. #3
    Join Date
    Oct 2006
    Posts
    6
    Quote Originally Posted by rvt
    Hey,

    you need to know that PostgreSQL create a query plan based on various variables, one of them is the statistical size of each table.

    So when you run the above SQL and you only insert one record,
    it might just well be that PostgreSQL create a query plan that is totally different then when you load each table up with a sensible a mouth of records. Often you will see that when tables are really small that PostgreSQL will not use an index at all but simple will issue a table scan rather then a index scan.

    Ries
    You missed the question completely. The question was about table inheritance and not sequential vs index scan. The question was: Can I make the FOREIGN KEY look in all the tables that descend from "Object" like the select * from "Object" does? Or just simply: make the above code run without errors.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote from the manual at http://www.postgresql.org/docs/8.3/s...l-inherit.html

    "All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited."

  5. #5
    Join Date
    Oct 2006
    Posts
    6
    Quote Originally Posted by shammat
    "All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited."
    I know that. But when you isue a SELECT * FROM "Object" You get 1,2,3 (notice here that 3 is in table "A" that is a descendant of "Object". But when you try to insert into "A" (4,3) you get an error "insert or update on table "A" violates foreign key constraint "FK_A_to_Object", Key (Ref_Object)=(3) is not present in table "Object".

    So, can i make the FOREIGN KEY "FK_A_to_Object" look into "A" for "ObjectID" and not only in "Object"?

  6. #6
    Join Date
    Oct 2006
    Posts
    6
    It is just not possible See PostgreSQL Documentation under "5.8.1. Caveats".

  7. #7
    Join Date
    Mar 2008
    Posts
    14
    Quote Originally Posted by Marin007
    You missed the question completely. The question was about table inheritance and not sequential vs index scan. The question was: Can I make the FOREIGN KEY look in all the tables that descend from "Object" like the select * from "Object" does? Or just simply: make the above code run without errors.
    Ahhhh crap.... my bad

Posting Permissions

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