473,882 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

error messages for key constraint violations

I have the following three tables

DROP TABLE CALLTRAK.SERVIC E_CODES
@
CREATE TABLE CALLTRAK.SERVIC E_CODES (
CODE CHAR(1) NOT NULL
, CONSTRAINT SERVICE_CODES_P K
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_ACC T_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.SERVIC ES
@
CREATE TABLE CALLTRAK.SERVIC ES (
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.SERVIC E_CODES (CODE)
, CONSTRAINT SERVICES_UK1
UNIQUE (CALL_ID, SERVICE_CODE)
)
@

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

SQL0530N The insert or update value of the FOREIGN KEY
"CALLTRAK.SERVI CES.SERVICE_COD E_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.SERVI CE_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_P K 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
Jun 27 '08
15 8603
On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
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
Jun 27 '08 #11
Lennart wrote:
On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
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.d oc/doc/c0005301.html
Cheers,

Dave.
Jun 27 '08 #12
Ian
Lennart wrote:
On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
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).
Jun 27 '08 #13
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_CN T 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!"
Jun 27 '08 #14
>>On 6/27/2008 at 12:15 PM, in message
<4a************ *************** *******@i76g200 0hsf.googlegrou ps.com>,
Lennart<Er***** *************@g mail.comwrote:
On Jun 27, 8:00 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
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

Jun 28 '08 #15
On Jun 28, 4:51 am, "Frank Swarbrick"
<Frank.Swarbr.. .@efirstbank.co m>
[...]
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
Jun 28 '08 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2601
by: Tetsuji Ueda | last post by:
Hello, I'm currently porting an application using some other DB to MySQL. The database in question has several tables with foreign key relations. The application is written such that, on table INSERTs, if a foreign key relation is violated, the error message is parsed for the particular relation that is violated. As a simple example, say we have the tables: create table mother (mother_id int primary key);
6
10643
by: Larry Johnson | last post by:
I have two similar SQL Server databases each with a table named Payments. PaymentID is an identity field and the primary, unique, key in both tables. There is one other key but it is not unique. The same INSERT command works for one table but not the other. The error is "...didn't add 1 record due to key violations". Since the tables apper to be identical as far as I can tell, I suspect the error message really means something else. ...
14
10156
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought I should give back to the community by posting our findings. Thanks you all for all your help till now by posting problems and their solutions. ~Abhijit
0
2699
by: Janning Vygen | last post by:
Hi, i have a question about how to handle postgresql constraint errors in the client app. I found some mails in the archive about it, too. But i have still so many questions about how to do it, where to check it and how to display a good error message. I would love to hear some comments about my ideas:
21
2733
by: Karl O. Pinc | last post by:
FYI, It'd be nice if the error message from a REFERENCES constraint mentioned the column name into which the bad data was attempted to be inserted. In PostgreSQL 7.3: sandbox=> insert into foo (id, b) values (3, 2); ERROR: b_is_fkey referential integrity violation - key referenced from
1
6695
by: Mike Hunter | last post by:
(Please CC me on any replies as I'm not on the list) Hi, After a recent power failure, a program that uses a pgsql backend (netdisco) started to send me nastygrams. I tried the author's suggestion of running a VACUUM FULL ANALYZE VERBOSE;, but it still sends me the messages. The data in the database isn't too important, i.e. I'm willing to blow away data to try to fix the problem. Any suggestions?
7
2031
by: jacob navia | last post by:
Microsoft proposed recently (In the Technical Report 24173 presented to the Standards Comitee) a change in the C library in the sense of more security. Basically, missing size information is passed to the new primitives like strcat_s(), gets_s(), and many others. The good thing in this proposal is that the specifications of the new library are much more precise than in the existing library, an improvement that made me start...
5
3515
by: Jeff | last post by:
Okay, I'm still new to vb.net 2005 - throught this was a hardware problem, but now I don't know. (I'm having some problem with my newgroup provider, so hopefully this will go through) This problem just started about a week ago, before all was fine. I'm using the code below to access a mysql database. On the line indicated when the connection is opened, the application has been sporatically drawing an error. It doesn't occur very often -...
12
5690
by: Franz Hose | last post by:
the following program, when compiled with gcc and '-std=c99', gcc says test.c:6: error: jump into scope of identifier with variably modified type that is, it does not even compile. lcc-win32, on the other hand, reports Warning test.c: 7 unreachable code
0
9932
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11109
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9558
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7957
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5782
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5979
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4602
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.