Repository navigation
check constraint fails but import succeedes #955
Description
I have the following sql script which when I load it, it successfully commits.
There should be an error. The first INSERT INTO fails with a check constraint and the record is ignored.
Expected result is for the transaction to fail.
BEGIN TRANSCTION;
CREATE TABLE types (
uuid TEXT DEFAULT (lower(hex(randomblob(4))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(6)))) CHECK(uuid regexp '^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$'),
description TEXT NOT NULL,
PRIMARY KEY(uuid)
);
INSERT INTO types (uuid,description) VALUES ('6d15a5615-0000-43ff-b4f2-762b0dbfb4e6', 'Substitution');
INSERT INTO types (uuid,description) VALUES ('615a5615-0001-43ff-b4f2-762b0dbfb4e6', 'URL');
INSERT INTO types (uuid,description) VALUES ('615a5615-0002-43ff-b4f2-762b0dbfb4e6', 'Date');
COMMIT;
Activity
Improve error handling in execution of SQL commands
Improve error handling in execution of SQL commands
Improve error handling in execution of SQL commands
MKleusberg commentedon Jan 31, 2017
You're right! There are multiple problems with our SQL import. These couple of lines you've posted there were quite valuable for me because they revealed three problems at once. So thanks a lot for this 😃
The main problem here and for #957, too, is that we have to remove all transaction bits from your SQL code because DB4S always opens its own transaction right after opening a database file. That's necessary for the Write Changed and Revert Changes buttons. And unfortunately transactions can't be nested.
I've tried to fix all the bugs I could find and worked around the transaction problem by replacing them with savepoints. So I believe this should be fixed for you now but I'm not a 100% sure it's completely working for you now. So can you test tomorrow's nightly build and tell me if it's working for you with that version? 😃
mgrojo commentedon Oct 8, 2018
I've tested with your SQL code and it now raises an error:
I think we can close this. @mtissington If I'm wrong, you're free to reopen it providing your feedback.