The Rel Forum

A certain INSERT statement was correctly rejected, as follows:

insert SP relation{tuple{S# 'S99', P# 'P1',Qty 120}};

ERROR: Update will cause CONSTRAINT Ca to fail.

Line 1, column 48 near '120'

But when I next dropped the constraint in question, something funny happened, as you can see:

drop CONSTRAINT Ca ;

NOTICE: Inserted 1 tuple.

Ok.


Hugh

I'll fix this in the next update.

Since I posted this one I've discovered that when an insert fails because of a mistake that gives rise to a constraint violation, and you then correct that mistake, the second insert reports twice as many tuples as it should have. It appears to have counted the ones on the failed insert as well as those on the successful one!

Hugh

HughDarwen wrote:Since I posted this one I've discovered that when an insert fails because of a mistake that gives rise to a constraint violation, and you then correct that mistake, the second insert reports twice as many tuples as it should have. It appears to have counted the ones on the failed insert as well as those on the successful one!

Indeed. Internally, a database update always occurs within a transaction, wherein the database update is executed, then the registers that track the number of tuples inserted/updated/deleted are incremented, then the database constraints are checked. If any constraints are violated, the transaction is rolled back, otherwise the transaction is committed. Unfortunately, a line of code was omitted, so the registers should have been cleared when the transaction was rolled back, but they weren't. Hence, their totals were being erroneously printed and carried over to the next transaction.