current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have created a database in PostgreSQL (8.4 - I need to use this version because I want to use MapFish which does not (yet) support 9.0) which has some inherited tables:

CREATE TABLE stakeholder
(
    pk_stakeholder integer DEFAULT nextval('stakeholder_seq') NOT NULL,
    fk_stakeholder_type integer NOT NULL,
    name character varying(255) NOT NULL,
    CONSTRAINT stakeholder_primarykey PRIMARY KEY (pk_stakeholder),
    CONSTRAINT stakeholder_fk_stakeholder_type FOREIGN KEY (fk_stakeholder_type)
            REFERENCES stakeholder_type (pk_stakeholder_type) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE NO ACTION
);
CREATE TABLE individual
(
    firstname character varying(50),
    fk_title integer,
    email1 character varying (100),
    email2 character varying (100),
    phone1 character varying (50),
    phone2 character varying (50),
    CONSTRAINT individual_primarykey PRIMARY KEY (pk_stakeholder),
    CONSTRAINT individual_fk_title FOREIGN KEY (fk_title)
            REFERENCES individual_title (pk_individual_title) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE NO ACTION
) INHERITS (stakeholder)

(as learned from an earlier question, I'm using a seperate table (stakeholder_pk) to keep track of my primary keys using triggers)

Now I'd like to reflect my database in SQLAlchemy (0.7.1):

meta.metadata.reflect(bind=engine)
table_stakeholder = meta.metadata.tables["stakeholder"]
table_individual = meta.metadata.tables["individual"]
stakeholder_mapper = orm.mapper(Stakeholder, table_stakeholder,
    polymorphic_on=table_stakeholder.c.fk_stakeholder_type,
    polymorphic_identity='stakeholder')
orm.mapper(Individual, table_individual, inherits=stakeholder_mapper,
    polymorphic_identity='individual')

This however results in an sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships between 'stakeholder' and 'individual'.

Now I've seen some examples where they use the primary key of the child tables (in my case: individual) as a foreign key to point at the primary key of the parent table (stakeholder). However, PostgreSQL will not let me do this, saying that this would violate a foreign key constraint since the primary key in the parent table (stakeholder) is not there (?).

So now I'm pretty much stuck and after hours of searching for a solution I'm starting to lose track of it. Is this a problem in PostgreSQL (similar to the primary key & inheritance issue) or is it because of SQLAlchemy? Or is it just me doing something fundamentally wrong?

share|improve this question

1 Answer 1

up vote 1 down vote

It is in PostgreSQL:

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.

These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.

http://www.postgresql.org/docs/9.0/interactive/ddl-inherit.html

Is it possible to drop triggers and to have in individual:

    pk_stakeholder integer DEFAULT nextval('stakeholder_seq') NOT NULL, 
...
    CONSTRAINT stakeholder_primarykey PRIMARY KEY (pk_stakeholder),

This will not stop individual to have pk_stakeholder that exists in stakeholder if you update pk_stakeholder later. So here triggers are required to stop update (easier) or to check.

share|improve this answer
    
Thanks for the answer, although I'm not sure if I understand this correctly. I already have those lines in my individual (the first one inherited and the primary key with a different name). Couldn't the problem be that looking at stakeholder in pgAdminIII, Rows (counted) are 0. But if I query SELECT * FROM stakeholder, all rows are there? Thank you very much –  lukas Jul 13 '11 at 7:32
    
I thing you can try to add first line and replace second. Or to remove INHERITS and do thinks good old way. –  jordani Jul 13 '11 at 17:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.