Hi!
Can anybody help?
I have a parent child hierarchy I model with 2 tables
CMS_ITEM - items can have child items
CMS_DEP - dependency data (parent child)

CMS Item holds the item information, like id, file name, file type
CMS_DEP holds teh dependency data parent id, child (file name and type - cant be ID as the child might not yet be on the system, but we do know its name and type which together will uniquely identify an item)
Items can have 0 or more children
Items can have 0 or more parents (usually 1 or 0)

CREATE TABLE CMS_ITEM (
ID VARCHAR(37) NOT NULL,
FILE_TYPE INTEGER NOT NULL,
FILE_NAME VARCHAR(255) NOT NULL
)

CREATE TABLE CMS_DEP (
PARENT VARCHAR(37) NOT NULL,
CHILD_TYPE SMALLINT NOT NULL, -- really FILE_TYPE
CHILD_NAME VARCHAR(255) NOT NULL
)

-- PRIMARY KEYS
ALTER TABLE CMS_ITEM ADD CONSTRAINT PK_CMS_ITEM PRIMARY KEY (ID)
ALTER TABLE CMS_DEP ADD CONSTRAINT PK_CMS_DEP PRIMARY KEY (PARENT, CHILD_TYPE, CHILD_NAME)

-- UNIQUE CONSTRAINTS
ALTER TABLE CMS_ITEM ADD CONSTRAINT UQ_CMS_DEP_TYPE_NAME UNIQUE (FILE_TYPE, FILE_NAME)<

-- FOREIGN KEYS
ALTER TABLE CMS_DEP ADD CONSTRAINT FK_CMS_DEP_CMS_ITEM FOREIGN KEY (PARENT_UID) REFERENCES CMS_ITEM (ID) ON DELETE CASCADE;
ALTER TABLE CMS_DEP ADD CONSTRAINT FK_CMS_DEP_CMS_ITEM_CHILD FOREIGN KEY (CHILD_TYPE, CHILD_NAME) REFERENCES CMS_ITEM (FILE_TYPE, FILE_NAME) NOT ENFORCED;

Now I have a procedure that takes a set of dependencies (in a string) is supposed only insert dependencies in the CMS_DEP table if they do not already exist... and indeed this is the case if a row already exists in the table before then it will not insert it.
However -- if the 'set' of dependencies supplied to the stored procedure (SP)
has a duplicate then th einser is attempted - and fails with a duplicate warning.

Here is a simplified version of the SP
Please note that << is used here as a statement terminator

CREATE PROCEDURE CMS_SP_UPDATE_FILE_DEPENDENCIES (
IN P_PARENT_FILE_ID VARCHAR(37),
IN P_PARENT_FILE_NAME VARCHAR(255),
IN P_PARENT_FILE_TYPE INTEGER,
IN P_DEPENDENCIES VARCHAR(4000) ) -- set of dependencies (file name, file type pairs separated by : and | like 'CHILD.1:14|CHILd.2:15'
SPECIFIC CMS_SP_UPDATE_FILE_DEPENDENCIES
LANGUAGE SQL
BEGIN
IF NOT EXISTS (SELECT ID FROM CMS_ITEM WHERE ID = P_PARENT_FILE_ID)
THEN
INSERT INTO CMS_ITEM (ID, FILE_TYPE, FILE_NAME, CREATION_TIME_STAMP, PLAN_DESCRIPTION) VALUES
(P_PARENT_FILE_ID, SMALLINT(P_PARENT_FILE_TYPE), P_PARENT_FILE_NAME, P_CREATION_TIME, P_PLAN_DESCRIPTION);
END IF;

IF LENGTH(P_DEPENDENCIES) <> 0
THEN
-- This is the problem bit:
INSERT INTO CMS_DEP (PARENT, CHILD_NAME, CHILD_TYPE)
-- create a temporay table of dependencies from the P_DEPENDENCIES param
WITH T(P_PARENT_FILE_ID, NM, VAL) AS
(
-- CMS_TF_GET_NV_PAIRS is a general purpose UDF and splits the :|separated pairs out of P_DEPENDENCIES and returns them as a table (NM = child name, VAL = child type)
SELECT P_PARENT_FILE_UID, NM, VAL FROM TABLE(CMS_TF_GET_NV_PAIRS( P_DEPENDENCIES))
)
SELECT P_PARENT_FILE_UID, NM, VAL
FROM T
WHERE NOT EXISTS -- I.E. dont insert if already exists in CMS_DEP
(
SELECT 1
FROM CMS_DEP C
WHERE P_PARENT_FILE_ID = C.PARENT AND NM = C.CHILD_NAME AND VAL = C.CHILD_TYPE
);
END IF;
END<<

Test:
CALL CMS_SP_UPDATE_FILE_DEPENDENCIES (
'MCF.1', --IN P_PARENT_FILE_UID VARCHAR(37),
'MCF.1.NAME', --IN P_PARENT_FILE_NAME VARCHAR(255),
12, --IN P_PARENT_FILE_TYPE INTEGER,
'ECF.1:14|ECF.2:14')<< --IN P_DEPENDENCIES VARCHAR(4000) )<<

-- that works - adds the rows to the 2 tables as expected

CALL CMS_SP_UPDATE_FILE_DEPENDENCIES (
'MCF.1', --IN P_PARENT_FILE_UID VARCHAR(37),
'MCF.1.NAME', --IN P_PARENT_FILE_NAME VARCHAR(255),
12, --IN P_PARENT_FILE_TYPE INTEGER,
'ECF.2:14|ECF.3:14|')<< --IN P_DEPENDENCIES VARCHAR(4000) )<<

-- that works - succeeds but does NOT add any rows to the tables - as expected.

-- this below fails:
CALL CMS_SP_UPDATE_FILE_DEPENDENCIES (
'MCF.1', 'MCF.1.NAME', 12, 'ECF.4:14|ECF.4:14|')<<

I get:
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 "CMS_DEP" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.61.65


It seems that if there are duplicates in the paremeter rather than the table already having the duplicate then the insert is attempted and it fails.
Not sure what is happeneing here - is the update done in batch, but the check at outset?

?? Ouch! Do I have to use a cursor - which I would avoid for efficiency purposes if at all possible


Help!

T

Use the MERGE statement.

Andy

So, why is that a surprise? You check for duplicates, don't find any, then attempt to insert duplicates. Clearly, you also need to eliminate duplicates from the set that you are inserting.

thanks folks -
the Merge idea is interesting and should sort it - thanks Andy - I will have a go at that
I reckon my existing SQL should detect the duplicate in the parameter
as essentially it is 'merging' the two tables (CMS_DEP and the temporary T)

So I would have expected the if not exists to have dissallowed the second (duplicate) insert. I am not sure how the check is applied - as it clearly is not for the second parameter item.

If anyone can explain that I would appreciate it

T

Are you familiar with the DISTINCT operator?


The set of rows to insert is built before the insert, if not physically, at least logically, so there are no duplicates at that point.

right tried the merge
works as expected on the first 2 tests but fails on the third as the insert did
i.e.
CALL CMS_SP_UPDATE_FILE_DEPENDENCIES ('MCF.1', 'MCF.1.NAME', 12, '12-05-2010_16:20:30', 'PLN DESC', 'ECF.4:14|ECF.4:14|')<<

yields:
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 "CMS_DEP" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.61.65


I modified the SP like:
CREATE OR REPLACE PROCEDURE CMS_SP_UPDATE_FILE_DEPENDENCIES (
IN P_PARENT_FILE_ID VARCHAR(37),
IN P_PARENT_FILE_NAME VARCHAR(255),
IN P_PARENT_FILE_TYPE INTEGER,
IN P_DEPENDENCIES VARCHAR(4000) )
SPECIFIC CMS_SP_UPDATE_FILE_DEPENDENCIES
LANGUAGE SQL
BEGIN
IF NOT EXISTS (SELECT ID FROM CMS_ITEM WHERE ID = P_PARENT_FILE_ID)
THEN
INSERT INTO CMS_ITEM (ID, FILE_TYPE, FILE_NAME, CREATION_TIME_STAMP, PLAN_DESCRIPTION)
VALUES (P_PARENT_FILE_UID, SMALLINT(P_PARENT_FILE_TYPE), P_PARENT_FILE_NAME, P_CREATION_TIME, P_PLAN_DESCRIPTION);
END IF;

IF LENGTH(P_DEPENDENCIES) <> 0
THEN
MERGE INTO CMS_DEP AS D
USING ( SELECT VAL, NM FROM TABLE(CMS_TF_GET_NV_PAIRS( P_DEPENDENCIES))) AS S
ON
D.CHILD_NAME = S.NM AND
D.CHILD_TYPE = S.VAL AND
D.PARENT_ID = P_PARENT_FILE_ID
WHEN NOT MATCHED THEN
INSERT VALUES(P_PARENT_FILE_ID, S.VAL, S.NM);
END IF;
END<<

Ah Captain Smollett I presume
yes if I use distinct on the merge - then I can eliminate the duplicate in the parameter

will try now

ta

T

Thanks Captain Smollett and Andy

using the merge coupled with a select distinct as below sorts it.
I aggree it should be a precondition, but in the application event the belts and braces need belt and braces!

I modified the SP like:
CREATE OR REPLACE PROCEDURE CMS_SP_UPDATE_FILE_DEPENDENCIES (
IN P_PARENT_FILE_ID VARCHAR(37),
IN P_PARENT_FILE_NAME VARCHAR(255),
IN P_PARENT_FILE_TYPE INTEGER,
IN P_DEPENDENCIES VARCHAR(4000) )
SPECIFIC CMS_SP_UPDATE_FILE_DEPENDENCIES
LANGUAGE SQL
BEGIN
IF NOT EXISTS (SELECT ID FROM CMS_ITEM WHERE ID = P_PARENT_FILE_ID)
THEN
INSERT INTO CMS_ITEM (ID, FILE_TYPE, FILE_NAME, CREATION_TIME_STAMP, PLAN_DESCRIPTION)
VALUES (P_PARENT_FILE_UID, SMALLINT(P_PARENT_FILE_TYPE), P_PARENT_FILE_NAME, P_CREATION_TIME, P_PLAN_DESCRIPTION);
END IF;

IF LENGTH(P_DEPENDENCIES) <> 0
THEN
MERGE INTO CMS_DEP AS D
USING ( SELECT DISTINCT VAL, NM FROM TABLE(CMS_TF_GET_NV_PAIRS( P_DEPENDENCIES))) AS S
ON
D.CHILD_NAME = S.NM AND
D.CHILD_TYPE = S.VAL AND
D.PARENT_ID = P_PARENT_FILE_ID
WHEN NOT MATCHED THEN
INSERT VALUES(P_PARENT_FILE_ID, S.VAL, S.NM);
END IF;
END<<