I'm using a closure table to manage an organisation structure. The organisation
table is defined as:
CREATE TABLE ORGANISATION
(
ORGID DOM_INTLARGE NOT NULL,
ORG_NME DOM_VARCHARMEDIUM NOT NULL,
LEVEL_NUM DOM_INTSMALL NOT NULL,
INUSE DOM_BOOLN,
CONSTRAINT PK_ORGANISATION PRIMARY KEY (ORGID),
CONSTRAINT UN_ORGNME UNIQUE (ORG_NME, LEVEL_NUM)
);
and the closure table as:
CREATE TABLE ORGCHART
(
PARENTID DOM_INTLARGE NOT NULL,
CHILDID DOM_INTLARGE NOT NULL,
DEPTH DOM_INTSMALL NOT NULL,
CONSTRAINT PK_ORGCHART PRIMARY KEY (PARENTID, CHILDID),
CONSTRAINT FK_ORG2CHCHILD FOREIGN KEY (CHILDID) REFERENCES ORGANISATION
(ORGID),
CONSTRAINT ORG2CHPARENT FOREIGN KEY (PARENTID) REFERENCES ORGANISATION
(ORGID));
If I use the statements in the following stored procedure to move a part in
the middle of the structure (where DEPTH =2 in the ORGCHART table) I get a
PRIMARY or UNIQUE KEY constraint "PK_ORGCHART" constraint error as the delete
statement hasn't removed all the required rows.
SET TERM ^ ;
CREATE PROCEDURE MOVEORGANISATION_OLD
(
IN_ORGTOMOVE BIGINT,
IN_WHERETOMOVE BIGINT
)
AS
BEGIN
DELETE FROM orgchart
WHERE childid IN (SELECT childid
FROM OrgChart
WHERE parentid = :IN_OrgToMove)
AND parentid IN (SELECT parentid
FROM OrgChart
WHERE childid = :IN_OrgToMove
AND parentid != childid);
INSERT INTO OrgChart (ParentId, ChildId, depth)
SELECT supertree.ParentId, subtree.ChildId, subtree.DEPTH + supertree.DEPTH + 1
FROM OrgChart supertree
CROSS JOIN OrgChart subtree
WHERE supertree.ChildId = :IN_WhereToMove
AND subtree.ParentId = :IN_OrgToMove;
END
^
SET TERM ; ^
However if I first store a list of the rows to be delete from ORGCHART table
into a temporary table defined as:
CREATE TABLE TMP_ORGCHANGE
(
ORGID DOM_INTLARGE,
PARENTID DOM_INTLARGE
);
and then use it in the where clause criteria of the delete statement
everything works as expected (see revise procedure below).
SET TERM ^ ;
CREATE PROCEDURE MOVEORGANISATION
(
IN_ORGTOMOVE BIGINT,
IN_WHERETOMOVE BIGINT
)
AS
BEGIN
/*List records to be removed into temporary table */
insert into TMP_ORGCHANGE (parentid, orgid)
select parentid, childid FROM orgchart
WHERE childid IN (SELECT childid
FROM OrgChart
WHERE parentid = :IN_OrgToMove)
AND parentid IN (SELECT parentid
FROM OrgChart
WHERE childid = :IN_OrgToMove
AND parentid != childid);
/* now delete records from ORGCHART */
delete from ORGCHART
where parentid in (select parentid from TMP_ORGCHANGE)
and childid in (select orgid from TMP_ORGCHANGE);
/* create the new links */
INSERT INTO OrgChart (ParentId, ChildId, depth)
SELECT supertree.ParentId, subtree.ChildId, subtree.DEPTH + supertree.DEPTH + 1
FROM OrgChart supertree
CROSS JOIN OrgChart subtree
WHERE supertree.ChildId = :IN_WhereToMove
AND subtree.ParentId = :IN_OrgToMove;
END
^
SET TERM ; ^
Is there something that I'm doing wrong in the first procedure that prevents
all relevant records from being deleted.
Here is a sample dataset to illustrate. If I pass the values 5,2 to the first
procedure the operation fails but passing them to the second procedure it
succeeds.
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('1', 'Whole Organisation', '0', NULL, NULL);
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('2', 'Div1', '1', '1', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('3', 'Div2', '1', '1', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('4', 'Dir11', '2', '2', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('5', 'Dir21', '2', '3', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('6', 'Spec111', '3', '4', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('7', 'Spec112', '3', '4', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('8', 'Spec211', '3', '5', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('9', 'Spec212', '3', '5', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '1', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('2', '2', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '2', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('3', '3', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '3', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('4', '4', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('2', '4', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '4', '2');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('5', '5', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('3', '5', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '5', '2');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('6', '6', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('4', '6', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('2', '6', '2');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '6', '3');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('7', '7', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('4', '7', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('2', '7', '2');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '7', '3');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('8', '8', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('5', '8', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('3', '8', '2');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '8', '3');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('9', '9', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('5', '9', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('3', '9', '2');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '9', '3');
