error messages for key constraint violations

[Frank Swarbrick]

I have the following three tables

DROP TABLE CALLTRAK.SERVICE_CODES
@
CREATE TABLE CALLTRAK.SERVICE_CODES (
CODE CHAR(1) NOT NULL
, CONSTRAINT SERVICE_CODES_PK
PRIMARY KEY (CODE)
, DESCRIPTION VARCHAR(50) NOT NULL
)
@

DROP TABLE CALLTRAK.CALLS
@
CREATE TABLE CALLTRAK.CALLS (
CALL_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT CALLS_PK PRIMARY KEY (CALL_ID)
, DATETIME TIMESTAMP NOT NULL
WITH DEFAULT
, CUST_NBR DECIMAL(9) NOT NULL
, BILL_BRCH DECIMAL(3) NOT NULL
, ACCT_BRCH DECIMAL(3) NOT NULL
, BILL_ACCT DECIMAL(10) NOT NULL
, ACCT_TYPE CHAR(1) NOT NULL
, WAIVE_CHG_YN CHAR(1) NOT NULL
, CONSTRAINT WAIVE_CHG_YN
CHECK(WAIVE_CHG_YN IN ('Y','N'))
, MULTI_ACCT_CALL_IND CHAR(1) NOT NULL
, CONSTRAINT MULTI_ACCT_CALL_IND
CHECK(MULTI_ACCT_CALL_IND IN ('N','B','C','E'))
, MULTI_ACCT_ORIG_DATETIME TIMESTAMP
, COMMENTS VARCHAR(54) NOT NULL
, BILL_ACCT_TYP CHAR(1) NOT NULL
, OPERATOR CHAR(3) NOT NULL
)
@

DROP TABLE CALLTRAK.SERVICES
@
CREATE TABLE CALLTRAK.SERVICES (
SERVICES_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT SERVICES_PK
PRIMARY KEY (SERVICES_ID)
, CALL_ID INTEGER NOT NULL
, CONSTRAINT CALL_ID_FK
FOREIGN KEY (CALL_ID)
REFERENCES CALLTRAK.CALLS (CALL_ID)
ON DELETE CASCADE
, SERVICE_CODE CHAR(1) NOT NULL
, CONSTRAINT SERVICE_CODE_FK
FOREIGN KEY (SERVICE_CODE)
REFERENCES CALLTRAK.SERVICE_CODES (CODE)
, CONSTRAINT SERVICES_UK1
UNIQUE (CALL_ID, SERVICE_CODE)
)
@

If I try to insert a row into CALLTRAK.SERVICES where the SERVICE_CODE value
is not present in CALLTRAK.SERVICE_CODES I get a nice, useful error
message:

SQL0530N The insert or update value of the FOREIGN KEY
"CALLTRAK.SERVICES.SERVICE_CODE_FK" is not equal to any value of the parent
key of the parent table. SQLSTATE=23503

But if I try to insert a row that causes a unique (or primary) key violation
I get something like this:

SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "CALLTRAK.SERVICE_CODES" from having duplicate values for the index
key. SQLSTATE=23505

I would think that it would return the name of the constraint that was
violated. In this case "SERVICE_CODES_PK". And in fact, when I follow the
guidance of how to get the name of the constraint, it returns it to me.
EG:

SELECT INDNAME, INDSCHEMA
FROM SYSCAT.INDEXES
WHERE IID = 1
AND TABSCHEMA = 'CALLTRAK'
AND TABNAME = 'SERVICE_CODES'
;
INDNAME INDSCHEMA

---------------- ---------
SERVICE_CODES_PK CALLTRAK

So my question is, why is this manual work (writing the above query)
necessary? Why doesn't DB2 do it itself?

My other (loaded) question is does anyone have any comments on the names of
my constraints? Good? Bad? Who cares?

Thanks,
Frank

[Dave Hughes]

Frank Swarbrick wrote:

[snip]

So my question is, why is this manual work (writing the above query)
necessary? Why doesn't DB2 do it itself?

Good question - it's a feature I'd like to see as well (in fact,
another place I'd really like to see something similar is when LOAD
spits out really helpful stuff like "...incompatible with column 3 in
the destination table...", I go and look up the third column in the
table only to remember (inevitably later on) that that particular
message uses 0-based column numbers ... grrr ...

My other (loaded) question is does anyone have any comments on the
names of my constraints? Good? Bad? Who cares?

I usually name all my primary keys PK - in DB2 constraint names (like
column names) are specific to a table (unsurprising given that a
constraint is specific to a table). Saves having to remember or figure
out names if I need to temporarily drop a PK. I do the same as you for
foreign keys: _FK suffix. For checks I tend to just use a _CK suffix (I
used to use things like a _BOOL suffix if the check effectively made
the column boolean, e.g. IN (0, 1), but I find its so rare that I want
to implement two or more check constraints on a column, that a simple
_CK suffix is sufficient).
Cheers,

Dave.

[Lennart]

Frank Swarbrick wrote:
[...]

>
So my question is, why is this manual work (writing the above query)
necessary? Why doesn't DB2 do it itself?

That would indeed be nice. My guess is that it depends on the fact
that primary key and unique is implemented via unique indexes, and
that you can have unique indexes without having a constraint.

My other (loaded) question is does anyone have any comments on the names of
my constraints? Good? Bad? Who cares?

Ideally I would like to name my constraints after my business rules
(i.e. logical level). In practice however it is often difficult to
find a short descriptive name for a business rule, so I tend to name
them from a physical point of view. Pretty much the way you do. One
problem though is that table names can be much longer than constraint
names (anyhow in V8.2, haven't checked V9.5 yet), so I often end up
using abbreviations. Even though it is nice to have good names on
constraints, most important thing is that they have a name at all. I
really dislike the auto generated names, because it makes it
impossible to safely identify a constraint or index. Comparing
databases, preparing upgrades etc is a nightmare otherwise.

A slightly related issue is whether to create constraints inside the
table definition, or to alter the table . I use the latter one because
it makes it possible to design indexes used in constraints the way I
want them. Example:

CREATE TABLE T (c int not null);
CREATE UNIQUE INDEX XPK_T ON T (c) CLUSTER ALLOW REVERSE SCANS ...;
ALTER TABLE T ADD CONSTRAINT XPK_T PRIMARY KEY (c);
/Lennart

[Serge Rielau]

I can explain the mechanics please don't understand it as an excuse. I'd
like to see this fixed myself...

The error you are getting for a constraint violation is originating from
the a raise_error() function injected by the compiler. So DB2 runtime
has full knowledge of the names involved here.
Unique constraint violations are tested "down in the bowels" of the
system. For space reasons there are no fluffy names around at that point.

Could some higher level function trap the error and do the SQL needed to
fill in the holes? Yes, absolutely.

There is actually an effort underway to improve consumability especially
around error-situations. What we are doing is mining our PMRs and
working our way down from the top offenders.
(Those who have scratched their heads over e.g. a helpful "I don't like
you MQT! *pffft*" from DB2 know what I'm talking about. ;-)
So if want to submit a PMR it would help raising general awareness of
this case. I have passed this thread on nonetheless.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Frank Swarbrick]

>>On 6/25/2008 at 6:49 PM, in message
<_9******************************@posted.plusnet >, Dave
Hughes<da**@waveform.plus.comwrote:

Frank Swarbrick wrote:

[snip]

>So my question is, why is this manual work (writing the above query)
necessary? Why doesn't DB2 do it itself?

Good question - it's a feature I'd like to see as well (in fact,
another place I'd really like to see something similar is when LOAD
spits out really helpful stuff like "...incompatible with column 3 in
the destination table...", I go and look up the third column in the
table only to remember (inevitably later on) that that particular
message uses 0-based column numbers ... grrr ...

Haha.

>My other (loaded) question is does anyone have any comments on the
names of my constraints? Good? Bad? Who cares?

I usually name all my primary keys PK - in DB2 constraint names (like
column names) are specific to a table (unsurprising given that a
constraint is specific to a table). Saves having to remember or figure
out names if I need to temporarily drop a PK. I do the same as you for
foreign keys: _FK suffix. For checks I tend to just use a _CK suffix (I
used to use things like a _BOOL suffix if the check effectively made
the column boolean, e.g. IN (0, 1), but I find its so rare that I want
to implement two or more check constraints on a column, that a simple
_CK suffix is sufficient).

I thought of doing the same for the PK. Then I investigated and noticed
that the index name that implicitly created will be named after the name of
the PK constraint, but only if it's unique (within the schema, I assume).
For example:

CREATE TABLE CALLTRAK.CALLS (
CALL_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT CALLS_PK PRIMARY KEY (CALL_ID)
)

CREATE TABLE CALLTRAK.SERVICES (
SERVICES_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT SERVICES_PK
PRIMARY KEY (SERVICES_ID)
)

This creates index CALLTRAK.CALLS_PK and index CALLTRACK.SERVICES_PK.

When both of the PK constraints have the same name, such as PK, then we get
CALLTRAK.PK as the index for the first table and
"CALLTRAK.SQL<somethingunique>" instead.

Thanks for the thoughts.

Frank

[Frank Swarbrick]

>>On 6/26/2008 at 5:28 AM, in message
<6c*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

I can explain the mechanics please don't understand it as an excuse. I'd
like to see this fixed myself...

The error you are getting for a constraint violation is originating from

the a raise_error() function injected by the compiler. So DB2 runtime
has full knowledge of the names involved here.
Unique constraint violations are tested "down in the bowels" of the
system. For space reasons there are no fluffy names around at that
point.

Could some higher level function trap the error and do the SQL needed to

fill in the holes? Yes, absolutely.

There is actually an effort underway to improve consumability especially

around error-situations. What we are doing is mining our PMRs and
working our way down from the top offenders.
(Those who have scratched their heads over e.g. a helpful "I don't like
you MQT! *pffft*" from DB2 know what I'm talking about. ;-)
So if want to submit a PMR it would help raising general awareness of
this case. I have passed this thread on nonetheless.

Thanks for the info and thoughts on future possible directions, Serge! I'll
see what my DBA things about raising a PMR on it (and perhaps others).

Frank

[Ian]

Dave Hughes wrote:

Frank Swarbrick wrote:

[snip]

>So my question is, why is this manual work (writing the above query)
necessary? Why doesn't DB2 do it itself?

Good question - it's a feature I'd like to see as well (in fact,
another place I'd really like to see something similar is when LOAD
spits out really helpful stuff like "...incompatible with column 3 in
the destination table...", I go and look up the third column in the
table only to remember (inevitably later on) that that particular
message uses 0-based column numbers ... grrr ...

My favorite has always been the "there was an error in row 9839-1" and
"row 9839-1 corresponds to row 150006". These row numbers are
especially useful in DPF, where the row numbers are counted
per-partition (i.e. after splitting).

[Dave Hughes]

Frank Swarbrick wrote:

>On 6/25/2008 at 6:49 PM, in message

<_9******************************@posted.plusnet >, Dave
Hughes<da**@waveform.plus.comwrote:

[snip]

I usually name all my primary keys PK - in DB2 constraint names
(like column names) are specific to a table (unsurprising given
that a constraint is specific to a table). Saves having to remember
or figure out names if I need to temporarily drop a PK. I do the
same as you for foreign keys: _FK suffix. For checks I tend to just
use a _CK suffix (I used to use things like a _BOOL suffix if the
check effectively made the column boolean, e.g. IN (0, 1), but I
find its so rare that I want to implement two or more check
constraints on a column, that a simple _CK suffix is sufficient).

I thought of doing the same for the PK. Then I investigated and
noticed that the index name that implicitly created will be named
after the name of the PK constraint, but only if it's unique (within
the schema, I assume). For example:

CREATE TABLE CALLTRAK.CALLS (
CALL_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT CALLS_PK PRIMARY KEY (CALL_ID)
)

CREATE TABLE CALLTRAK.SERVICES (
SERVICES_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, CONSTRAINT SERVICES_PK
PRIMARY KEY (SERVICES_ID)
)

This creates index CALLTRAK.CALLS_PK and index CALLTRACK.SERVICES_PK.
When both of the PK constraints have the same name, such as PK, then
we get CALLTRAK.PK as the index for the first table and
"CALLTRAK.SQL<somethingunique>" instead.

Ah, I don't usually run into that problem as I always declare
constraints separately to the table, and implement my "own" unique
indexes for the PK. Historically I did this because ALLOW REVERSE SCANS
wasn't the default for indexes on DB2 v8 (although there's no need
nowadays as it is the default since v9), and to permit INCLUDE columns
for commonly retrieved stuff. Hence, taking one of your original
examples, I'd do something like the following:

CREATE SCHEMA CALLTRAK;
SET SCHEMA CALLTRAK;

CREATE TABLE CALLS (
ID INTEGER GENERATED ALWAYS AS IDENTITY,
START TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CUSTOMER DECIMAL(9) NOT NULL,
BILL_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_TYPE CHAR(1) NOT NULL,
BILL_ACCOUNT DECIMAL(10) NOT NULL,
BILL_ACCOUNT_TYPE CHAR(1) NOT NULL,
WAIVE_CHARGE CHAR(1) NOT NULL,
MULTI_ACCOUNT_CALL CHAR(1) NOT NULL,
MULTI_ACCOUNT_ORIGIN TIMESTAMP DEFAULT NULL,
COMMENTS VARCHAR(54) NOT NULL,
OPERATOR CHAR(3) NOT NULL
);

CREATE UNIQUE INDEX CALLS_PK
ON CALLS(ID)
INCLUDE (START, CUSTOMER, ...);

ALTER TABLE CALLS
ADD CONSTRAINT PK PRIMARY KEY (ID)
ADD CONSTRAINT WAIVE_CHARGE_CK CHECK (WAIVE_CHARGE IN ('N', 'Y'))
ADD CONSTRAINT MULTI_ACCOUNT_CALL_CK CHECK (MULTI_ACCOUNT_CALL IN
('N', 'B', 'C', 'E'));

COMMENT ON TABLE CALLS IS 'Contains details of all calls taken';
COMMENT ON CALLS (
ID IS 'Unique identifier of a call',
START IS 'Timestamp of the start of the call',
CUSTOMER IS '...',
...
);

RUNSTATS ON TABLE CALLTRAK.CALLS
WITH DISTRIBUTION
AND DETAILED INDEXES ALL
SET PROFILE ONLY;

....

I've also tweaked some names and other bits according to my own foibles
or preferences:

I've always hated using abbreviations; in 9.5 the limits on most things
are finally large enough that I don't have to worry about hitting any.
Hopefully my guesses at the expansion of the original abbreviations are
correct :-).

I also removed schema names and added SET SCHEMA. I tend to define a
schema's content in a single SQL script. If I want to test that script
without affecting the original schema, I can simply tweak the SET
SCHEMA line at the top and try it.

Unfortunately, this theory doesn't work as I tend to include stats
profile definitions with my CREATE TABLE statements (as shown above)
.... and RUNSTATS requires a qualified table name (no idea why - it's
another of those "little things" I'd like to see fixed - if IMPORT and
LOAD can use CURRENT SCHEMA, why can't RUNSTATS?).

Oh, and the COMMENT ON statements - because all tables should be
properly documented (although I admit I have yet to practice this
preaching properly myself... ahem ;-)

Anyway - that just about sums up the styles I've come to use over time.
Cheers,

Dave.

[Lennart]

On Jun 26, 7:08 pm, "Dave Hughes" <[e-mail]:
[...]

>
COMMENT ON TABLE CALLS IS 'Contains details of all calls taken';
COMMENT ON CALLS (
ID IS 'Unique identifier of a call',
START IS 'Timestamp of the start of the call',
CUSTOMER IS '...',
...
);

Ahh, I like this one, haven't seen this before. Much nicer than my
usual:

comment on column <schema>.CALLS.IS IS 'Unique identifier of a call';

Thanx for the info
/Lennartt

[Frank Swarbrick]

>>On 6/26/2008 at 11:08 AM, in message
<T9******************************@posted.plusnet >, Dave
Hughes<da**@waveform.plus.comwrote:

Ah, I don't usually run into that problem as I always declare
constraints separately to the table, and implement my "own" unique
indexes for the PK. Historically I did this because ALLOW REVERSE SCANS
wasn't the default for indexes on DB2 v8 (although there's no need
nowadays as it is the default since v9), and to permit INCLUDE columns
for commonly retrieved stuff. Hence, taking one of your original
examples, I'd do something like the following:

Probably a good idea. The main reason I declared them in the CREATE TABLE
statement is so I could see the column constraint rights next to the column
definition.

CREATE SCHEMA CALLTRAK;
SET SCHEMA CALLTRAK;

CREATE TABLE CALLS (
ID INTEGER GENERATED ALWAYS AS IDENTITY,
START TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CUSTOMER DECIMAL(9) NOT NULL,
BILL_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_BRANCH DECIMAL(3) NOT NULL,
ACCOUNT_TYPE CHAR(1) NOT NULL,
BILL_ACCOUNT DECIMAL(10) NOT NULL,
BILL_ACCOUNT_TYPE CHAR(1) NOT NULL,
WAIVE_CHARGE CHAR(1) NOT NULL,
MULTI_ACCOUNT_CALL CHAR(1) NOT NULL,
MULTI_ACCOUNT_ORIGIN TIMESTAMP DEFAULT NULL,
COMMENTS VARCHAR(54) NOT NULL,
OPERATOR CHAR(3) NOT NULL
);

CREATE UNIQUE INDEX CALLS_PK
ON CALLS(ID)
INCLUDE (START, CUSTOMER, ...);

ALTER TABLE CALLS
ADD CONSTRAINT PK PRIMARY KEY (ID)
ADD CONSTRAINT WAIVE_CHARGE_CK CHECK (WAIVE_CHARGE IN ('N', 'Y'))
ADD CONSTRAINT MULTI_ACCOUNT_CALL_CK CHECK (MULTI_ACCOUNT_CALL IN
('N', 'B', 'C', 'E'));

COMMENT ON TABLE CALLS IS 'Contains details of all calls taken';
COMMENT ON CALLS (
ID IS 'Unique identifier of a call',
START IS 'Timestamp of the start of the call',
CUSTOMER IS '...',
...
);

RUNSTATS ON TABLE CALLTRAK.CALLS
WITH DISTRIBUTION
AND DETAILED INDEXES ALL
SET PROFILE ONLY;

...

I've also tweaked some names and other bits according to my own foibles
or preferences:

I've always hated using abbreviations; in 9.5 the limits on most things
are finally large enough that I don't have to worry about hitting any.
Hopefully my guesses at the expansion of the original abbreviations are
correct :-).

Mostly. I can see why you might have this preference. I kind of have the
opposite preference. I use abreviations because I gate typing long names!
:-)

(Hey, ID and PK are both abbreviations!)

I actually ended up with separate ALTER statements for each of the keys, and
then one for all of the check constraints. I did this because on the
SERVICES table I had both a PK and a unique key and noticed that (when I did
the alters together) it told me that it used an existing index for the
unique key. I found this nice, so I separated them and also got a similar
message for the PK. This is nice because it lets you know you defined your
index correctly!

I have a question on the INCLUDE clause in the PK index. What is it for?
Specifically? I read the docs, but I don't really 'grok' it. Can you
explain it's usefulness?

I also removed schema names and added SET SCHEMA. I tend to define a
schema's content in a single SQL script. If I want to test that script
without affecting the original schema, I can simply tweak the SET
SCHEMA line at the top and try it.

Makes sense. I actually did create some new tables without affecting the
original ones, but I did it by changing the names of the tables themselves.
You idea is better. :-)

Oh, and the COMMENT ON statements - because all tables should be
properly documented (although I admit I have yet to practice this
preaching properly myself... ahem ;-)

Good idea. I wonder how many people follow it. ;-)

Anyway - that just about sums up the styles I've come to use over time.

Thanks for the tips!!

Frank

[Lennart]

On Jun 27, 8:00 pm, "Frank Swarbrick" <[e-mail]>
wrote:
[...]

>
I have a question on the INCLUDE clause in the PK index. What is it for?
Specifically? I read the docs, but I don't really 'grok' it. Can you
explain it's usefulness?

If the columns you require in a query is *included* in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

/Lennart

[Dave Hughes]

Lennart wrote:

On Jun 27, 8:00 pm, "Frank Swarbrick" <[e-mail]>
wrote:
[...]

I have a question on the INCLUDE clause in the PK index. What is
it for? Specifically? I read the docs, but I don't really 'grok'
it. Can you explain it's usefulness?

If the columns you require in a query is included in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

Precisely - for more information see the "Types of Index Access" in the
InfoCenter:

http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.admin.perf.doc/doc/c0005301.html
Cheers,

Dave.

[Ian]

Lennart wrote:

On Jun 27, 8:00 pm, "Frank Swarbrick" <[e-mail]>
wrote:
[...]

>I have a question on the INCLUDE clause in the PK index. What is it for?
Specifically? I read the docs, but I don't really 'grok' it. Can you
explain it's usefulness?

If the columns you require in a query is *included* in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

Just to add: The include columns are not part of the index *key*, it's
just that the value is stored on the index page (so you can avoid table
access, as Lennart said).

[Ian]

Dave Hughes wrote:

>

>>Oh, and the COMMENT ON statements - because all tables should be
properly documented (although I admit I have yet to practice this
preaching properly myself... ahem ;-)

Good idea. I wonder how many people follow it. ;-)

Hehe - not many! Database documentation has become something of a
personal obsession for me since I started writing a generic tool for
generating documentation from DB2 databases a while back. Let's just
say the reception hasn't been so much "cool" as "non-existent" (with
the exception of one brave chap who's been a magnificent beta tester
and suggestion maker).

I find that the biggest problem is that people will put terrible
descriptions. For example, a column called MISC_CD_CNT would end up
with a decription like "MISC_CD_CNT for the customer". Thanks, that
really helps.

I don't have much evidence, but this phenomenon appears to come from the
fact that many people don't ever build logical models, they just build a
physical model in ERwin and say, "cool! I'm a data modeler!"

[Frank Swarbrick]

>>On 6/27/2008 at 12:15 PM, in message
<4a**********************************@i76g2000hsf. googlegroups.com>,
Lennart<Er******************@gmail.comwrote:

On Jun 27, 8:00 pm, "Frank Swarbrick" <[e-mail]>
wrote:
[...]

>>
I have a question on the INCLUDE clause in the PK index. What is it

for?

>Specifically? I read the docs, but I don't really 'grok' it. Can you
explain it's usefulness?

If the columns you require in a query is *included* in the index, db2
doesn't have to do a fetch from the table since all data required is
already at hand.

Ah!

So I should declare a unique index and include all other columns with it,
then.

:-)

Just kidding!

Thanks for the info. I understand now. Now I just need to decide if and
when it makes sense to do it.

Frank

[Lennart]

On Jun 28, 4:51 am, "Frank Swarbrick"
<[e-mail]>
[...]

Thanks for the info. I understand now. Now I just need to decide if and
when it makes sense to do it.

It's a trade off between the extra space used, and minimizing I/O. IMO
good candidates are small catalog tables, where the non indexed
column(s) are used in select statements frequently. Other than that I
tend to add include columns during performance test as part of
optimization.
/Lennart

Frank