I have the following three tables
DROP TABLE CALLTRAK.SERVICE_CODES
@[EMAIL PROTECTED]
TABLE CALLTRAK.SERVICE_CODES (
CODE CHAR(1) NOT NULL
, CONSTRAINT SERVICE_CODES_PK
PRIMARY KEY (CODE)
, DESCRIPTION VARCHAR(50) NOT NULL
)
@[EMAIL PROTECTED]
TABLE CALLTRAK.CALLS
@[EMAIL PROTECTED]
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
)
@[EMAIL PROTECTED]
TABLE CALLTRAK.SERVICES
@[EMAIL PROTECTED]
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)
)
@[EMAIL PROTECTED]
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
|
