Peter Geoghegan <peter.geoghegan86@gmail.com> writes:
> On 26 January 2013 22:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> BTW, one thing that struck me in a quick look-through is that the
>> ERRCODE_FOREIGN_KEY_VIOLATION patches seem to inconsistently send
>> either the PK or FK rel as the "errtable". Is this really per spec?
>> I'd have sort of expected that the reported table ought to be the one
>> that the constraint belongs to, namely the FK table.
> Personally, on the face of it I'd expect the "inconsistency" to simply
> reflect the fact that the error related to the referencing table or
> referenced table.
I looked in the spec a bit, and what I found seems to support my
recollection about this. In SQL99, it's 19.1 <get diagnostics
statement> that defines the usage of these fields, and I see
f) If the value of RETURNED_SQLSTATE corresponds to integrity constraint violation, transaction
rollback- integrity constraint violation, or a triggered data change violation that was caused
bya violation of a referential constraint, then:
i) The values of CONSTRAINT_CATALOG and CONSTRAINT_SCHEMA are the <catalog name> and the
<unqualifiedschema name> of the <schema name> of the schema containing the constraint or
assertion.The value of CONSTRAINT_NAME is the <qualified identifier> of the constraint or assertion.
ii) Case:
1) If the violated integrity constraint is a table constraint, then the values of
CATALOG_NAME,SCHEMA_ NAME, and TABLE_NAME are the <catalog name>, the <unqualified
schemaname> of the <schema name>, and the <qualified identifier> or <local table name>,
respectively, of the table in which the table constraint is contained.
The notion of a constraint being "contained" in a table is a bit weird;
I guess they mean contained in the table's schema description. Anyway
it seems fairly clear to me that it's supposed to be the table that the
constraint belongs to, and that has to be the FK table not the PK table.
regards, tom lane