Re: cross-table constraints?
От | Kevin Hunter Kesling |
---|---|
Тема | Re: cross-table constraints? |
Дата | |
Msg-id | 521E3CB6.7070802@ncsu.edu обсуждение исходный текст |
Ответ на | Re: cross-table constraints? (David Johnston <polobo@yahoo.com>) |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: