On 02/01/2016 01:23 PM, David G. Johnston wrote:
> On Mon, Feb 1, 2016 at 2:11 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>wrote:
>
> On 02/01/2016 12:52 PM, Dane Foster wrote:
>
> On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com