Re: cross-table constraints?

At 3:33pm -0400 Tue, 27 Aug 2013, David Johnston wrote:
> Kevin Hunter Kesling wrote
>>   CREATE TABLE cost (
>>     id          integer NOT NULL PRIMARY KEY,
>>     period_id   integer NOT NULL REFERENCES vintage (id),
>>     process_id  integer NOT NULL REFERENCES process (id),
>>     value       real    NOT NULL,
>>
>>     UNIQUE (period_id, process_id)
>>   );
>
> A check constraint can only reference the same table as on which it
> is defined so you will most likely, in some place, define either a
> trigger or wrap the relevant constraint checking into an API function
> and only modify the relevant database items via that function.
>
> That said you can create a relation containing all the valid
> combinations and then use a foreign key constraint on the cost side
> so that onlydefined combinations can be costed out.

I had thought about that, but that would require transforming the
trigger logic from the check constraint to the creation of rows in the
"valid" table.  Is that a more "correct" approach then simply performing
the check via a trigger on the cost table?  If so, why?

> Two other comments to consider. The "cost" relation defined above, if
> you de-normalize it via the foreign keys, ends up having two
> "analysis_id" fields - the one on vintage and the one on process.

This is a really good observation!  Thank you for pointing it out.

> It is possible that these two field may not be in sync - in addition
> to the "minimum period" error you already have identified.

Heh, while I will do my utmost to get my application logic correction,
this is a constant worry for me.  It would be /really/ nice to be able
to figure this out at the DB level.  Hopefully I can work around this
with triggers.

> The reason for this problem is that you are using artificial keys
> for your relationships instead of natural keys.
>
> I may espouse on this more later but cannot at the moment. While
> surrogate keys are not evil they are also not something to be used
> lightly and in as complex a model as this using natural keys does
> have its advantages. Since you can define multi-column foreign keys
> the same analysis_id on the cost table can be related to other tables
> in two separately defined  "references".

I really like this point.  I really do.  Unfortunately, the large detail
that I left out from my question is that I've created this model through
Django, which (currently) requires a single column be the primary key
for it's ORM logic.

     [1] "Each model requires exactly one field to have
          primary_key=True."  (where 'field' = DB column)

     [2] Currently Django models only support a single column in this
         set, denying many designs where the natural primary key of a
         table is multiple columns. Django currently can't work with
         these schemas; they must instead introduce a redundant
         single-column key (a “surrogate” key), forcing applications
         to make arbitrary and otherwise-unnecessary choices about
         which key to use for the table in any given instance.

The choice of Django has drastically helped my project in other areas,
but leaves some of the data integrity and modeling issues to the
application logic.  I suppose "one pays their monies and makes their
choices!"

Thank you very much for your helpful insights.

Kevin

[1]
https://docs.djangoproject.com/en/dev/topics/db/models/#automatic-primary-key-fields

[2] https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys