On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote:
> From the spec:
>
> "The character string value returned in an SQLSTATE parameter
> comprises a 2-character class value followed by a 3-character subclass
> value, each with an implementation-defined character set that has a
> one-octet character encoding form and is restricted to <digit>s and
> <simple Latin upper case letter>s. Table 32, *SQLSTATE class and
> subclass values*, specifies the class value for each condition and
> the subclass value or values for each class value."
>
> and:
>
> "If a subclass value is not specified for a condition, then either
> subclass '000' or an implementation-defined subclass is returned."
Thanks, I'd not found that specified--it matches up to what I'd found
PG and other databases doing. Still doesn't really describe the
engineering rational behind it though.
> From the table, the 23xxx series is for integrity constraint
> violations, but they appear not to have gotten too specific about
> breaking that down; thereby leaving it as an implementation choice:
>
> integrity constraint violation 23
> (no subclass) 000
> restrict violation 001
Yes; but somewhere along the line we've got exactly the same integrity
constraint violation sqlcodes as DB2 (and Derby, but that's not very
surprising as they're both IBM). Can't find anybody else trying very
hard though.
> Anyway, it was a bad suggestion that we provide a way to specify a
> SQLSTATE to use for a constraint failure. I do think that some field
> which could be used for that purpose would be good. Preferably
> something which could be specified in the declaration of the
> constraint.
I still stand by my assertion that the constraint name is sufficient for
the original purpose.
-- Sam http://samason.me.uk/