键约束违规的错误消息 [英] error messages for key constraint violations
问题描述
我有以下三张桌子
DROP TABLE CALLTRAK.SERVICE_CODES
@
CREATE TABLE CALLTRAK.SERVICE_CODES（
代码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
一直作为身份生成
，CONSTRAINT CALLS_PK PRIMARY KEY（ CALL_ID）
，DATETIME TIMESTAMP非空白
带默认值
，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
，操作员CHAR（3）NOT NULL
）
@
DROP TABLE CALLTRAK。服务
@
CREATE TABLE CALLTRAK.SERVICES（
SERVICES_ID INTEGER NOT NULL
始终作为身份
，CONSTRAINT SERVICES_PK
PRIMARY KEY（SERVICES_ID）
，CALL_ID INTEGER NOT NULL
，CONSTRAINT CALL_ID_FK
FOREIGN KEY（CALL_ID）
参考CALLTRAK.CALLS（CALL_ID）
ON DELETE CASCADE
，SERVICE_CODE CHAR（1）NOT NULL
，CONSTRAINT SERVICE_CODE_FK
FOREIGN KEY（SERVICE_CODE）
参考CALLTRAK.SERVICE_CODES（CODE）
，CONSTRAINT SERVICES_UK1
独特（CALL_ID，SERVICE_CODE）
）
@
如果我尝试向CALLTRAK.SERVICES中插入一行，其中CALLTRAK.SERVICE_CODES中不存在SERVICE_CODE值
我得到一个很好的，有用的错误
消息：
SQL0530N FOREIGN KEY的插入或更新值
" CALLTRAK.SERVICES.SERVICE_CODE_FK"不等于父表的父
键的任何值。 SQLSTATE = 23503
但是如果我尝试插入导致唯一（或主要）密钥违规的行
我会得到这样的结果：
SQL0803N由DELETE语句引起的INSERT语句，UPDATE语句或
外键更新中的一个或多个值无效，因为
主键，唯一约束或由1标识的唯一索引。约束
table" CALLTRAK.SERVICE_CODES"来自索引的重复值
key。 SQLSTATE = 23505
我认为它会返回违反
的约束的名称。在这种情况下，SERVICE_CODES_PK。事实上，当我按照
指导如何获得约束的名称时，它会将它返回给我。
EG：
SELECT INDNAME，INDSCHEMA
来自SYSCAT.INDEXES
WHERE IID = 1
AND TABSCHEMA ='''CALLTRAK''
AND TABNAME =''SERVICE_CODES''
;
INDNAME INDSCHEMA
---- ------------ ---------
SERVICE_CODES_PK CALLTRAK
所以我的问题是，为什么是这个手工工作（写上面的查询）
必要吗？为什么DB2本身不做呢？
我的其他（加载）问题是否有人对
我的约束的名称有什么评论？好？坏？谁在乎呢？
谢谢，
Frank
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
推荐答案
Frank Swarbrick写道：
[snip]
Frank Swarbrick wrote:
[snip]
所以我的问题是，为什么这本手册有效（写上面的查询）
必要吗？为什么DB2本身不做呢？
So my question is, why is this manual work (writing the above query)
necessary? Why doesn''t DB2 do it itself?
好问题 - 这是我想看到的一个功能（事实上，
另一个地方我'' d真的很想看到类似的东西，当LOAD
吐出真正有用的东西时，比如...与目标表格中的第3列不相容...... ，我去查看
表中的第三列只是为了记住（不可避免地在后面）那个特定的
消息使用从0开始的列号... grrr ...
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?
我通常将所有主键命名为PK - 在DB2约束名称中（如
列名）特定于表（并不奇怪，因为
约束是特定于表格。）如果我需要暂时放弃PK，请保存或记住
名称。我做山姆e为你的
外键：_FK后缀。对于检查，我倾向于使用_CK后缀（I
用于使用类似_BOOL后缀的东西，如果检查有效地使得b / b
列布尔值，例如IN（0， 1），但我发现它非常罕见，我想要
来实现对列的两个或更多检查约束，一个简单的
_CK后缀就足够了。 />
干杯，
戴夫。
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.
弗兰克Swarbrick写道：
[...]
Frank Swarbrick wrote:
[...]
>
所以我的问题是，为什么这是手工工作（写上面的查询）
必要吗？为什么DB2本身不做呢？
>
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?
理想情况下，我想在我的业务规则之后命名我的约束
（即逻辑级别）。然而，在实践中，通常很难为商业规则找到一个简短的描述性名称，因此我倾向于从物理角度命名
。几乎就是你的方式。一个
的问题是，表名可能比约束更长
名称（无论如何在V8.2中，还没有检查过V9.5），所以我经常以缩写形式结束
。尽管在
约束上有好名字很好，但最重要的是它们有一个名字。我b $ b真的不喜欢自动生成的名字，因为它使得它无法安全地识别约束或索引。比较
数据库，准备升级等是一个噩梦。
一个稍微相关的问题是是否在
内创建约束表定义，或改变表。我使用后一个因为
它可以设计在约束中使用的索引，就像我想要它们一样。示例：
CREATE TABLE T（c int not null）;
CREATE UNIQUE INDEX XPK_T ON T（c）CLUSTER ALLOW REVERSE SCANS ......;
ALTER TABLE T添加约束XPK_T PRIMARY KEY（c）;
/ Lennart
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
我可以解释一下这个机制我不明白这是一个借口。我希望
喜欢看到我自己解决这个问题...
你因违反约束而得到的错误源于
编译器注入的raise_error（）函数。所以DB2运行时
完全了解这里涉及的名称。
唯一约束违规被测试在大便中。
系统。由于空间原因，此时没有松散的名字。
一些更高级别的函数可以捕获错误并执行所需的SQL
填写孔？是的，绝对。
实际上正在努力提高可消费性，特别是在错误情况下的b $ b。我们正在做的是挖掘我们的PMR和
从顶级违规者那里下来。
（那些已经摸不着头脑的人，例如有帮助的我不喜欢不喜欢
你MQT！* pffft *"来自DB2知道我在说什么。;-)
所以如果想要提交PMR它会帮助提高对这种情况的一般意识
。尽管如此，我已经通过了这个帖子。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
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
这篇关于键约束违规的错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
