Re: Inheritance and Referencing troubles... (confusing problem)

On Wed, 9 May 2001, Christian Marschalek wrote:

> Hey there...
>
> I have something like this:
>
> CREATE TABLE user_table (u_id serial primary key, u_name name);
> CREATE TABLE customer_table (c_number int4) INHERTIS (user_table);
> CREATE TABLE shadow_table (shadow_username char(15) primary key,
> shadow_password char(8), shadow_u_id int4 REFERENCES !and here comes the
> question!
>
> When I reference to customer_table (u_id) it does not work... Because
> customer table has no in fact no u_id

Hmm, if you make a unique index on customer_table(u_id), you should be
able to make a reference to it then.  The primary key does not inherit
currently.

> When I reference to user_table (u_id) it does work... But When I insert
> something into the customer table and then try to insert something into
> the shadow table -> he can't find the corresponding number in the user
> table...

Right, because it's a direct reference to only the table specified
currently.  FK also doesn't currently inherit nicely.

> When I insert something directly into the user table i can insert
> someting corresponding to it in the shadow table... Funny thing here is
> that although u_id is a primary key I can insert keys with the same
> value. (there is one than more "1" when I select * from user table and
> inserted something into customer table... I don't even need to select
> from table* because he seem to follow the inheritance automatically
> now...)

Yep, the dups gets back to the primary key not inheriting.  You can only
have one row in user_table that has 1, but if you put more rows in the
child with value 1 you'll see dups doing the select * from user_table.
Primary key needs to mean unique across entire inheritance tree, I'm
not sure when that'll happen though.