Re: Check constraints and function volatility categories

On 02/01/2016 01:23 PM, David G. Johnston wrote:
> On Mon, Feb 1, 2016 at 2:11 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>wrote:
>
> On 02/01/2016 12:52 PM, Dane Foster wrote:
>
> On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
>
>
>
> As an example of where this leads see:
>
> http://www.postgresql.org/message-id/7224.1452275604@sss.pgh.pa.us
>
> ​Thanks for the heads up. The good news is all machine access to the
> data will be via functions and views so I can inline the
> constraint in
> the right places. In other news, this sucks! I have no idea what it
>
>
> I could see moving your constraint into a per row trigger.
>
>
> You'd need to basically replicate the current FK constraint setup but
> with custom queries...you need the insert/update trigger on the main
> table and then a insert/update/delete trigger on the referenced table to
> ensure that actions just rejected if the relevant detail on the main
> table isn't changed. Then decide whether you need something like "ON
> UPDATE/DELETE CASCADE" instead of the default 'reject' behavior.
>
> I take it you would need to ensure that these triggers are disabled
> during dump/restore but am not certain on that point.

Well this brings up another part to Danes post(that contained the
function definition):

"Unfortunately the "type" definition can't be expressed as a primary key
so I can't use foreign keys to enforce consistency."

Not sure what exactly is meant by "type", though I suspect it is this:
"SELECT type FROM discount_codes WHERE code ..."

FYI, I know type is non-reserved word, but I would avoid using it as a
column name. I went down that path and got myself confused in a hurry:)

In any case it should be pointed out that FKs do not necessarily have to
point to PKs:

http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html

"The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table"

>
> David J.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com