Thread: Reference with inheritance propagate data

Hello, All !

I have base table q_base_table with column (id bigint) which may be
inherited by users tables, primary key for both base table and derived
tables is id. Now I need for another table record_rubricator which has
to be referenced to base and derived tables, which way I have to do
it, because postgresql does not allow automatically propagate data ?

--
Best regards,
Sincerely yours,
Yuriy Rusinov.

On Wed, 2012-06-13 at 00:38 +0400, Yuriy Rusinov wrote:
> Hello, All !
>
> I have base table q_base_table with column (id bigint) which may be
> inherited by users tables, primary key for both base table and derived
> tables is id. Now I need for another table record_rubricator which has
> to be referenced to base and derived tables, which way I have to do
> it, because postgresql does not allow automatically propagate data ?

One foreign key cannot reference two tables.

Have you considered a design that does not use inheritance? For
instance, the users table could reference q_base_table, and then
record_rubricator could also reference q_base_table?

Also, I don't understand what you mean about propagating data. What data
do you want to propagate?

Regards,
    Jeff Davis

Hello, Jeff !

>
> One foreign key cannot reference two tables.
>
> Have you considered a design that does not use inheritance? For
> instance, the users table could reference q_base_table, and then
> record_rubricator could also reference q_base_table?

Unfortunately not, because this design does not developed by me and
these changes will result to inefficiency of all project.
>
> Also, I don't understand what you mean about propagating data. What data
> do you want to propagate?

We're need common numeration for primary key for all users tables, but
others columns may be different for tables.

--
Best regards,
Sincerely yours,
Yuriy Rusinov.

On Thu, Jun 14, 2012 at 2:08 AM, Yuriy Rusinov <yrusinov@gmail.com> wrote:
> Hello, Jeff !
>
>>
>> One foreign key cannot reference two tables.
>>
>> Have you considered a design that does not use inheritance? For
>> instance, the users table could reference q_base_table, and then
>> record_rubricator could also reference q_base_table?
>
> Unfortunately not, because this design does not developed by me and
> these changes will result to inefficiency of all project.
>>
>> Also, I don't understand what you mean about propagating data. What data
>> do you want to propagate?
>
> We're need common numeration for primary key for all users tables, but
> others columns may be different for tables.
>
When we forked LedgerSMB from SQL-Ledger they used a similar design
and it was a mess.  I don't want to go into the problems we figured
could exist or we heard about from users.

Luckily if all you are looking for is a common place for primary keys
there is a useful shim that we came up with:

1)  Create a table that stores the pkey value and what table it's in.
2)  Use triggers on other tables to maintain that data
3)  Use fkeys against either the other tables themselves (where it is
a local reference) or against the common table

It's not really ideal.  It's possible the data will get out of sync of
something goes wrong and then you will have problems but it seems to
work for the most part and without a significant performance headache.

Best Wishes,
Chris Travers

On Thu, 2012-06-14 at 13:08 +0400, Yuriy Rusinov wrote:
> We're need common numeration for primary key for all users tables, but
> others columns may be different for tables.

Will a sequence shared between the two tables solve this problem?

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

Regards,
    Jeff Davis