Re: Checking number of entries - Mailing list pgsql-general
From | Philip Warner |
---|---|
Subject | Re: Checking number of entries |
Date | |
Msg-id | 3.0.5.32.20000929171834.020fe100@mail.rhyme.com.au Whole thread Raw |
In response to | Re: Checking number of entries (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Checking number of entries
|
List | pgsql-general |
At 01:53 29/09/00 -0400, Tom Lane wrote: > >The canonical example is a check like > CHECK (mycol > (SELECT max(othertab.othercol) FROM othertab)) >declared as a constraint on mytab. I would argue that a CHECK constraint only applies to the table on which it is defined - basically a contract to ensure that certain conditions apply to changes on that table. Once you allow functions in constraints, you give up all hope of cross-compatibility, eg. Create Table tab1 (f1 integer); Create Function tot_of_tab1() returns integer as 'select cast(sum(f1) as int4) from tab1' language 'sql'; Alter Table tab1 add check(tot_of_tab1() > 0); zzz=# insert into tab1 values(1); INSERT 142380 1 zzz=# insert into tab1 values(-10); INSERT 142381 1 zzz=# select tot_of_tab1(); tot_of_tab1 ------------- -9 (1 row) zzz=# insert into tab1 values(-12); ERROR: ExecAppend: rejected due to CHECK constraint $1 This demonstrates the problem, and, I think, highlights a bug in the constraint checking code: the constraint should have failed on the second insert. Maybe the constraint is evaluate before the insert? >Should this mean that the constraint on mytab can prevent insertions >into othertab? I'd say not. Consider: weird_function() returns boolean as 'select current_time > ''16:00:00'''; This *could* be used to prevent updates occurring before 4pm - it says nothing about the validity of the data, it is just a rule about how updates can be performed. Does DB theory say what sort of contract CHECK constraints imply? >You can devise related paradoxes within a single table if constraints >involving aggregates are permitted. The basic problem is that it's >not clear which data values are the constrainers and which are the >constrainees. What's wrong with CHECK constraints only affecting the table on which they are defined? It seems better than (a) allowing back-door functions, and (b) preventing more sensible subselects. Sorry if this ground has already been covered... ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
pgsql-general by date: