Обсуждение: functions allowed in CHECK constraints
Hello, I just tried specifying a function in a check constraint, (not being able to use a subquery or otherwise referencing rowsin another (parent) table) and this worked. However reading the docs do not point to any such direction, on the contrary docs say that subselects are not allowed. e.g. this fails as expected : alter table crew_eval_rankset_rank ADD CONSTRAINT ranksetid_chk CHECK((SELECT NOT appliestoall FROM crew_eval_rankset cerWHERE cer.id=ranksetid)); ERROR: cannot use subquery in check constraint while this works unexpectedly : create function crew_eval_ranksets_check_appliestoall (ranksetid int) RETURNS BOOLEAN LANGUAGE sql AS $$ SELECT NOT appliestoall FROM crew_eval_rankset cer WHERE cer.id=ranksetid $$; alter table crew_eval_rankset_rank ADD CONSTRAINT ranksetid_chk CHECK(crew_eval_ranksets_check_appliestoall(ranksetid)); insert into crew_eval_rankset(setname,appliestoall) VALUES('all ranks','t'); insert into crew_eval_rankset_rank (ranksetid , rankid) VALUES(3,70); ERROR: new row for relation "crew_eval_rankset_rank" violates check constraint "ranksetid_chk" So, what's the point in forbidding the use of subselects if one can use functions? And OTOH if effectively doing so is badfor some reason, why let it happen with a function? Basically, I tried this after reading : https://stackoverflow.com/questions/21791675/foreign-key-constraint-with-some-column-values-residing-in-other-tables/22023533#22023533 ,I wouldn't have thought doing it after reading the docs. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
On 09/02/2018 11:04, Achilleas Mantzios wrote: > Hello, well it seems the intuition by reading the docs was correct, although not exactly explicit. A Trigger is the way to do this. > .... -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
On 09/02/2018 11:04, Achilleas Mantzios wrote:Hello,well it seems the intuition by reading the docs was correct, although not exactly explicit. A Trigger is the way to do this.....
I answered this on the first email you sent. Functions are black-boxes so its up to the author to ensure that the restrictions on check constraints remain met when using them.
David J.