agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Achilleas Mantzios <itdev@itdevel.internal.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: functions allowed in CHECK constraints
Date: Fri, 9 Feb 2018 10:37:18 +0200
Message-ID: <8d319874-ea46-d44f-29df-b6ecde7ce9c4@itdevel.internal.net> (raw)

Hello,

I just tried specifying a function in a check constraint, (not being able to use a subquery or otherwise referencing rows in 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 cer WHERE 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 bad for 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... , I wouldn't have thought 
doing it after reading the docs.

-- 
Achilleas Mantzios

view thread (6+ messages)  latest in thread

Message-ID: <8d319874-ea46-d44f-29df-b6ecde7ce9c4@itdevel.internal.net>
Permalink:  ../8d319874-ea46-d44f-29df-b6ecde7ce9c4@itdevel.internal.net/
Also on:    postgresql.org/message-id/8d319874-ea46-d44f-29df-b6ecde7ce9c4@itdevel.internal.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: itdev@itdevel.internal.net
  Subject: Re: functions allowed in CHECK constraints
  In-Reply-To: <8d319874-ea46-d44f-29df-b6ecde7ce9c4@itdevel.internal.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox