Re: Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 4:48 PM, Adrian Klaver <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 ..."
>
​Type in the context that I'm using it simply means the type of thing the
function is checking to see exists​ or not. The example I gave
unfortunately had a column named type that confuses the situation but in
actually that example is the only one that actually has a column named type
involved in evaluation.

> 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"
>
​I am aware of the fact that FKs don't have to point to PKs but they do
have to point to something backed by a unique indexed and that is what
makes them unsuitable for my needs. Here is an example where foreign keys
can't play a role, but this time as a description.

The system can send out surveys to solicit feedback. The admin can
customize the surveys and associate them w/ specific class events. The
constraint is this, once a survey has a respondent then the survey can no
longer be edited.

​I'm going to try out David's idea of using triggers to implement the more
complex constraints and inline the simpler ones in functions where
appropriate.

Regards,

Dane​