An SQL trap and how to get out of it

Today I spent about two hours trying to debug a complicated Priority procedure. After about half an hour, I managed to determine exactly where the problem lay, but I couldn't figure out why there was a problem. As it's cold and wet outside, I couldn't get up and go for a walk, turning the problem over in my head. Having a break for lunch didn't help either.

Here's the problematic code

INSERT INTO GLOB_TMPTREE (FATHER, SON, PIS, ORDI, USER)
SELECT :PART, ORDERITEMS.PART, TEC_ORDIFEATURES.OPTIONCODE,
ORDERITEMS.ORDI, SQL.USER
FROM ORDERITEMS, TEC_ORDIFEATURES, TEC_FEATURES
WHERE ORDERITEMS.ORDI = TEC_ORDIFEATURES.ORDI
AND TEC_ORDIFEATURES.FEATURE = TEC_FEATURES.FEATURE
AND TEC_FEATURES.FEATURECODE = 'PIS'
AND ORDERITEMS.ORD = :ORD
AND ORDERITEMS.TEC_U1 = :U1
AND ORDERITEMS.TEC_U2 = :U2
AND ORDERITEMS.ORDI <> :XLINE;

Leaving aside the specific details of the query, I am selecting data then entering it into a table. I checked (several times) that the actual query was returning data; even so, it was not being inserted into the table. The line before the quoted passage deletes all the lines in the table belonging to the current user so there shouldn't be a problem in inserting the data.

With the benefit of 100% hindsight, I can now say: when an insert fails, check the table's primary key. In this case, the primary key was composed of father, son and pis; it so happened that another user had tried to run the procedure but for some reason, the procedure didn't complete and certainly didn't clear up after itself. There were lines in the table for this other user - and it turned out that the primary key values for these lines were exactly the same as the ones I was trying to insert.

When I finally got to this stage, I deleted all the lines in the table (this is only a temporary table with a few lines at most); I then ran the procedure, and unsurprisingly it did exactly what it was supposed to do. I then added the 'user' field to the primary key which should prevent the same error from happening again.

So: when an insert fails, check the table's primary key! Maybe next time I'll remember this.