On 02/01/2016 01:23 PM, David G. Johnston wrote:
On Mon, Feb 1, 2016 at 2:11 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>wrote:
On 02/01/2016 12:52 PM, Dane Foster wrote:
On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>> wrote:
As an example of where this leads see:
http://www.postgresql.org/message-id/7224.1452275604@xxxxxxxxxxxxx
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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
