Обсуждение: Foreign key constraint question

On Fri, Jul 20, 2007 at 08:57:25AM -0500, Perry Smith wrote:
> I want to do something like this:
>
> ALTER TABLE companies ADD CONSTRAINT fk_companies_item_id
>              FOREIGN KEY (item_id, 'Company')
>              REFERENCES item_bases(item_id, item_type)
>              INITIALLY DEFERRED
>
> I could add a column to companies that is always set to "Company" but
> that seems like a waste.  I tried the above and I got a syntax error.

What purpose is the constraint intended to achieve?

--
Michael Fuhr

On Jul 20, 2007, at 9:06 AM, Michael Fuhr wrote:

> On Fri, Jul 20, 2007 at 08:57:25AM -0500, Perry Smith wrote:
>> I want to do something like this:
>>
>> ALTER TABLE companies ADD CONSTRAINT fk_companies_item_id
>>              FOREIGN KEY (item_id, 'Company')
>>              REFERENCES item_bases(item_id, item_type)
>>              INITIALLY DEFERRED
>>
>> I could add a column to companies that is always set to "Company" but
>> that seems like a waste.  I tried the above and I got a syntax error.
>
> What purpose is the constraint intended to achieve?

I am trying to create a polymorphic item.  item_bases holds the
item_id and item_type.  Different tables like companies, people, etc
will hold the data.  This example is for companies.  The item_type
does not hold the actual table name but holds the "class" name.

The idea is that the item_id will be the same in item_bases and in
companies (to pick an example).  I want to make sure that the
item_base points to an item and the item points back to the item_base.

I can add a check constraint to item_base to make sure that with the
item_id and item_type it references a record in the right table.  But
I can not defer that.  So, I plan to add to the companies table
first, then add to item_bases table.

So, with the the foreign key constraint above, I want to make sure
that (item_id, item_type) of companies matches an (item_id,
item_type) in item_bases but in companies, I do not have an item_type
column since it will always be a constant.

Can I add a column to the table somehow like: "Company" as
item_type  ?  That would achieve the same effect.

Thank you,
Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems

On Fri, 2007-07-20 at 09:27 -0500, Perry Smith wrote:
> On Jul 20, 2007, at 9:06 AM, Michael Fuhr wrote:
>
> > On Fri, Jul 20, 2007 at 08:57:25AM -0500, Perry Smith wrote:
> >> I want to do something like this:
> >>
> >> ALTER TABLE companies ADD CONSTRAINT fk_companies_item_id
> >>              FOREIGN KEY (item_id, 'Company')
> >>              REFERENCES item_bases(item_id, item_type)
> >>              INITIALLY DEFERRED
> >>
> >> I could add a column to companies that is always set to "Company" but
> >> that seems like a waste.  I tried the above and I got a syntax error.
> >
> > What purpose is the constraint intended to achieve?
>
> I am trying to create a polymorphic item.  item_bases holds the
> item_id and item_type.  Different tables like companies, people, etc
> will hold the data.  This example is for companies.  The item_type
> does not hold the actual table name but holds the "class" name.

Is there a reason you're storing the type (or, to be precise, a more
specific type) of the entity as a _value_ in the table that holds
attributes of the super type?

That seems limiting, and means you can only have one extra level of
specificity in your entity type.

Better to just not include the type of the entity as a value at all. If
you want only companies, join item_bases to companies and the join will
eliminate any non-company entities from the result (because the non-
company entities in item_bases will have no matching tuple in
companies).

The relational model handles inheritance and polymorphism very well if
you don't store types as values.

> The idea is that the item_id will be the same in item_bases and in
> companies (to pick an example).  I want to make sure that the
> item_base points to an item and the item points back to the item_base.
>
> I can add a check constraint to item_base to make sure that with the
> item_id and item_type it references a record in the right table.  But
> I can not defer that.  So, I plan to add to the companies table
> first, then add to item_bases table.
>

You can use a constraint trigger. The current docs say that's "not
intended for general use," but it will be available for general use in
8.3. And those can be deferred.

Regards,
    Jeff Davis

On Jul 20, 2007, at 7:01 PM, Jeff Davis wrote:

> On Fri, 2007-07-20 at 09:27 -0500, Perry Smith wrote:
>> On Jul 20, 2007, at 9:06 AM, Michael Fuhr wrote:
>>
>>> On Fri, Jul 20, 2007 at 08:57:25AM -0500, Perry Smith wrote:
>>>> I want to do something like this:
>>>>
>>>> ALTER TABLE companies ADD CONSTRAINT fk_companies_item_id
>>>>              FOREIGN KEY (item_id, 'Company')
>>>>              REFERENCES item_bases(item_id, item_type)
>>>>              INITIALLY DEFERRED
>>>>
>>>> I could add a column to companies that is always set to
>>>> "Company" but
>>>> that seems like a waste.  I tried the above and I got a syntax
>>>> error.
>>>
>>> What purpose is the constraint intended to achieve?
>>
>> I am trying to create a polymorphic item.  item_bases holds the
>> item_id and item_type.  Different tables like companies, people, etc
>> will hold the data.  This example is for companies.  The item_type
>> does not hold the actual table name but holds the "class" name.
>
> Is there a reason you're storing the type (or, to be precise, a more
> specific type) of the entity as a _value_ in the table that holds
> attributes of the super type?
>
> That seems limiting, and means you can only have one extra level of
> specificity in your entity type.
>
> Better to just not include the type of the entity as a value at
> all. If
> you want only companies, join item_bases to companies and the join
> will
> eliminate any non-company entities from the result (because the non-
> company entities in item_bases will have no matching tuple in
> companies).
>
> The relational model handles inheritance and polymorphism very well if
> you don't store types as values.

What if I have just an id for an item?  This will happen when another
table references an item.  How do I know what type it is?  Are you
suggesting I look in companies, people, etc, etc to find the type?
It would seem better to have a table that tells me the type.  Then
retrieve the item from the specified table.

>> The idea is that the item_id will be the same in item_bases and in
>> companies (to pick an example).  I want to make sure that the
>> item_base points to an item and the item points back to the
>> item_base.
>>
>> I can add a check constraint to item_base to make sure that with the
>> item_id and item_type it references a record in the right table.  But
>> I can not defer that.  So, I plan to add to the companies table
>> first, then add to item_bases table.
>>
>
> You can use a constraint trigger. The current docs say that's "not
> intended for general use," but it will be available for general use in
> 8.3. And those can be deferred.

I saw those but where scared away from them because of the "not for
general use".

With my somewhat limited experience, I figured that applied to me.

Thank you again for your help,
Perry

On Fri, 2007-07-20 at 19:18 -0500, Perry Smith wrote:
> > The relational model handles inheritance and polymorphism very well if
> > you don't store types as values.
>
> What if I have just an id for an item?  This will happen when another
> table references an item.  How do I know what type it is?  Are you
> suggesting I look in companies, people, etc, etc to find the type?
> It would seem better to have a table that tells me the type.  Then
> retrieve the item from the specified table.

Why do you need to know the type? The purpose of polymorphism is that,
if you are looking to access a set of polygons, you don't care whether
an individual shape is a triangle or a square, all you care is that it's
a polygon.

If you want to access triangles specifically, you join polygons to
triangles.

> > You can use a constraint trigger. The current docs say that's "not
> > intended for general use," but it will be available for general use in
> > 8.3. And those can be deferred.
>
> I saw those but where scared away from them because of the "not for
> general use".
>

I only mentioned it because in 8.3 it will be useful for general use. I
don't know what's changing about it between now and then, but it's
becoming "un-deprecated".

It's probably wise to stay away from them until 8.3.

Regards,
    Jeff

On Jul 20, 2007, at 19:37 , Jeff Davis wrote:

> I only mentioned it because in 8.3 it will be useful for general
> use. I
> don't know what's changing about it between now and then, but it's
> becoming "un-deprecated".

AFAIK, nothing's changed in the actual constraint trigger code: it's
just a doc change. No reason not to use them in 8.2.

Michael Glaesemann
grzm seespotcode net

Jeff Davis skrev:
> On Fri, 2007-07-20 at 19:18 -0500, Perry Smith wrote:
>>> The relational model handles inheritance and polymorphism very well if
>>> you don't store types as values.
>> What if I have just an id for an item?  This will happen when another
>> table references an item.  How do I know what type it is?  Are you
>> suggesting I look in companies, people, etc, etc to find the type?
>> It would seem better to have a table that tells me the type.  Then
>> retrieve the item from the specified table.
>
> Why do you need to know the type? The purpose of polymorphism is that,
> if you are looking to access a set of polygons, you don't care whether
> an individual shape is a triangle or a square, all you care is that it's
> a polygon.
>
> If you want to access triangles specifically, you join polygons to
> triangles.

What if, for instance, I want to render a list of shapes?

To render the shape, I need to get its data, to get its data, I need to
know what type it is. ISTM that the easiest way to achieve this is
storing the type info at the "top" of the table hierarchy.

This then gives us two evils to choose from wrt integrity checking:

1. Add the type column to these tables as well, restricting possible
values. This gives us integrity at the cost of verbosity and lack of
modularity (a type need to "know" its subtypes).

2. Leave integrity enforcement to the applications (and possibly write
stored procedures for it).

Both of these are reasonable implementation choices, I would say.

The OP wants to do 1, is only envisioning one level of inheritance, and
wants a shortcut for it.

Yours,

Nis Jorgensen

PS: Hi Jeff. Small world, isn't it?

Perry Smith wrote:
>>> I want to do something like this:
>>>
>>> ALTER TABLE companies ADD CONSTRAINT fk_companies_item_id
>>>              FOREIGN KEY (item_id, 'Company')
>>>              REFERENCES item_bases(item_id, item_type)
>>>              INITIALLY DEFERRED
>>>
>>> I could add a column to companies that is always set to "Company" but
>>> that seems like a waste.  I tried the above and I got a syntax error.

Can there be more than one row in "item_bases" with the same "item_id" but
different "item_type"s?

--
Lew

Nis Jørgensen wrote:

> What if, for instance, I want to render a list of shapes?
>
> To render the shape, I need to get its data, to get its data, I need to
> know what type it is. ISTM that the easiest way to achieve this is
> storing the type info at the "top" of the table hierarchy.

Try adding "tableoid" to the list of columns retrieved.  Even better,
cast that to regclass.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera skrev:
> Nis Jørgensen wrote:
>
>> What if, for instance, I want to render a list of shapes?
>>
>> To render the shape, I need to get its data, to get its data, I
>> need to know what type it is. ISTM that the easiest way to achieve
>> this is storing the type info at the "top" of the table hierarchy.
>
> Try adding "tableoid" to the list of columns retrieved.  Even better,
>  cast that to regclass.

This is if I use PG table inheritance, right? This might be a solution
to the problem of the OP. I have stayed away from that solution however,
mostly because of this bit from the documentation:

> A serious limitation of the inheritance feature is that indexes
> (including unique constraints) and foreign key constraints only apply
> to single tables, not to their inheritance children. This is true on
> both the referencing and referenced sides of a foreign key
> constraint.

This to me means that the builin inheritance is less powerful than what
I can build myself (although multiple inheritance is nice, and quite
hard to implement using the shared-fields model).

Nis

Well, what about using inheritence and relation identifiers?

Nis Jørgensen wrote:
> Alvaro Herrera skrev:
> > Nis Jørgensen wrote:
> >
> >> What if, for instance, I want to render a list of shapes?
> >>
> >> To render the shape, I need to get its data, to get its data, I
> >> need to know what type it is. ISTM that the easiest way to achieve
> >> this is storing the type info at the "top" of the table hierarchy.
> >
> > Try adding "tableoid" to the list of columns retrieved.  Even better,
> >  cast that to regclass.
>
> This is if I use PG table inheritance, right?

Right, that was what I was thinking.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Jul 22, 2007, at 10:35 AM, Alvaro Herrera wrote:

> Nis Jørgensen wrote:
>> Alvaro Herrera skrev:
>>> Nis Jørgensen wrote:
>>>
>>>> What if, for instance, I want to render a list of shapes?
>>>>
>>>> To render the shape, I need to get its data, to get its data, I
>>>> need to know what type it is. ISTM that the easiest way to achieve
>>>> this is storing the type info at the "top" of the table hierarchy.
>>>
>>> Try adding "tableoid" to the list of columns retrieved.  Even
>>> better,
>>>  cast that to regclass.
>>
>> This is if I use PG table inheritance, right?
>
> Right, that was what I was thinking.

Right now, I am pretty happy with what I have (but I do have some
extra data).

In what I call my item_base table, I have an item_id and item_type
(integer and string).  The type is not the table name but a Class
name.  I also have a table that goes from one to the other.

In each of my child (sub-class) tables I have the same tuple.  But,
for example, in the companies table, I have an added constraint that
it is set to "Company".  I have a "initially deferred" foreign
constraint of item_id and item_type from the child tables to the
item_base table.  The item_base table has a check constraint that
translates the item_type to a table name and then verifies that the
id in the referenced table exists.  I also have "on delete cascade"
set so they both disappear at the same time.

I do not have item_id in the item_base table of type serial.  Instead
I have a sequence number that the child tables get their id from (all
from the same sequence).  Then this id is put into the item_base
table.  This is because the child table entry must be created first,
then the base because I can not defer the check constraint (which
turns out not to be true but I didn't know that when I started down
this road).

Thank you all for helping,
Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems