regexsqlitedb-browser-sqlite

REGEXP in SQLite - problem while editing table

I started learning SqLite and encountered a problem with regular expressions. I use DB Browser. A table is created, the table has two fields: email and address. When writing the line "address" REGEXP '\S' in the address field to "Check", everything is saved, but when writing the same thing to the email field, an error appears (on the screenshot). I don't understand what's going on at all. In the first picture everything is fine, in the second one there is a problem. I don't understand why.

This is ok:

CREATE TABLE "Organizations" (
    "email" TEXT CHECK("email" REGEXP ''),
    "address"   TEXT CHECK("address" REGEXP '\S')
);

This is wrong:

CREATE TABLE "Organizations" (
    "email" TEXT CHECK("email" REGEXP '\S'),
    "address"   TEXT CHECK("address" REGEXP '\S')
);

This is OK

the problem

Solution

  • According to the screenshot the issue is that the data being copied has failed the CHECK constraint aka the RULE that says that there must be non whitespace characters. The source data thus does contain a column or columns that contain data that does not have whitespace characters.

    If you were use SELECT *, address REGEXP '\S' AS ADDR_CHK, email REGEXP '\S' AS EM_CHK FROM Organizations WHERE address NOT REGEXP '\S' OR email NOT REGEXP '\S'; before attempting to modify the table, then this would show the rows that will fail the CHECK constraints (if any, the error indicates that there are).

    Perhaps consider the following that demonstrates what is happening BUT utilises an INSERT OR IGNORE to allow data that does not conform to the constraint to not result in a failure:-

    DROP TABLE IF EXISTS Organizations;
CREATE TABLE IF NOT EXISTS "Organizations" (
    "email" TEXT CHECK("email" REGEXP '\S'),
    "address"   TEXT CHECK("address" REGEXP '\S')
);
INSERT OR IGNORE INTO Organizations VALUES ('X','X'),('','X') /* Not inserted (IGNOREd) due to check constraint*/,('X','') /* again not inserted due to check*/;
SELECT * FROM Organizations;

    The DB Browser log, of the above, is:-

    -- EXECUTING ALL IN 'SQL 1'
--
-- At line 1:
DROP TABLE IF EXISTS Organizations;
-- Result: query executed successfully. Took 4ms
-- At line 1:
CREATE TABLE IF NOT EXISTS "Organizations" (
    "email" TEXT CHECK("email" REGEXP '\S'),
    "address"   TEXT CHECK("address" REGEXP '\S')
);
-- Result: query executed successfully. Took 4ms
-- At line 5:
INSERT OR IGNORE INTO Organizations VALUES ('X','X'),
    ('','X') /*<<<<<<<<<< Not inserted (IGNOREd) due to check constraint*/,
    ('X','') /*<<<<<<<<<< again not inserted due to check*/;
-- Result: query executed successfully. Took 5ms, 1 rows affected
-- At line 8:
SELECT * FROM Organizations;
-- Result: 1 rows returned in 11ms

    i.e. The INSERT only shows 1 affected row (the 2 that fail the constraint have been IGNOREd).

    A get around could be to make the change to the table via SQL e.g.:-

    /* Cleanup just in case */
DROP TABLE IF EXISTS Organizations_old;
DROP TABLE IF EXISTS Organizations;
/* Create the Organisaztions table without the CHECK constraint on the address column */
CREATE TABLE IF NOT EXISTS "Organizations" (
    "email" TEXT CHECK("email" REGEXP '\S'),
    "address"   TEXT /*CHECK("address" REGEXP '\S') <<<<<<<<<< Note CHECK constraint done away with*/ 
);
/* Attempt to add various data */
/*  1 row that adheres to both check constraints i.e. X X */
/*  1 row that breaks (conflicts) with the email CHECK constraint */
/*  1 row that conflicts with the address CHECK constraint  (that has not been epplied )*/
INSERT OR IGNORE INTO Organizations VALUES ('X','X'),
    ('','X') /*<<<<<<<<<< Not inserted (IGNOREd) due to check constraint*/,
    ('X','') /*<<<<<<<<<< again not inserted due to check*/;
/* show the result i.e. 2 rows that were inserted (the '',x row having been ignored by the 1st INSERT)*/
SELECT * FROM Organizations;
/* Rename the original table */
ALTER TABLE Organizations RENAME TO Organizations_old;
/* Create the replacement/changed table */
CREATE TABLE IF NOT EXISTS "Organizations" (
    "email" TEXT CHECK("email" REGEXP '\S'),
    "address"   TEXT CHECK("address" REGEXP '\S')
);
/* Populate the new table using the existing data (2 rows BUT the 'X','' row will be IGNOREd)*/
INSERT OR IGNORE INTO Organizations SELECT * FROM Organizations_old;
/* Show the results (now only the 1 row that does not conflict with the CHECK constraint remains) */
SELECT * FROM Organizations;
/* Do not need the old table anymore */
DROP TABLE IF EXISTS Organizations_old;
/* Finally clean up the demo environment */
DROP TABLE IF EXISTS Organizations_old; /* should not exist*/
DROP TABLE IF EXISTS Organizations;

    The log from the above being:-

    -- EXECUTING SELECTION IN 'SQL 1'
--
-- At line 1:
/* Cleanup just in case */
DROP TABLE IF EXISTS Organizations_old;
-- Result: query executed successfully. Took 0ms
-- At line 2:
DROP TABLE IF EXISTS Organizations;
-- Result: query executed successfully. Took 2ms
-- At line 3:
/* Create the Organisaztions table without the CHECK constraint on the address column */
CREATE TABLE IF NOT EXISTS "Organizations" (
    "email" TEXT CHECK("email" REGEXP '\S'),
    "address"   TEXT /*CHECK("address" REGEXP '\S') <<<<<<<<<< Note CHECK constraint done away with*/ 
);
-- Result: query executed successfully. Took 2ms
-- At line 8:
/* Attempt to add various data */
/*  1 row that adheres to both check constraints i.e. X X */
/*  1 row that breaks (conflicts) with the email CHECK constraint */
/*  1 row that conflicts with the address CHECK constraint  (that has not been epplied )*/
INSERT OR IGNORE INTO Organizations VALUES ('X','X'),
    ('','X') /*<<<<<<<<<< Not inserted (IGNOREd) due to check constraint*/,
    ('X','') /*<<<<<<<<<< again not inserted due to check*/;
-- Result: query executed successfully. Took 2ms
-- At line 15:
/* show the result i.e. 2 rows that were inserted (the '',x row having been ignored by the 1st INSERT)*/
SELECT * FROM Organizations;
-- Result: 2 rows returned in 7ms
-- At line 17:
/* Rename the original table */
ALTER TABLE Organizations RENAME TO Organizations_old;
-- Result: query executed successfully. Took 4ms
-- At line 19:
/* Create the replacement/changed table */
CREATE TABLE IF NOT EXISTS "Organizations" (
    "email" TEXT CHECK("email" REGEXP '\S'),
    "address"   TEXT CHECK("address" REGEXP '\S')
);
-- Result: query executed successfully. Took 3ms
-- At line 24:
/* Populate the new table using the existing data (2 rows BUT the 'X','' row will be IGNOREd)*/
INSERT OR IGNORE INTO Organizations SELECT * FROM Organizations_old;
-- Result: query executed successfully. Took 3ms
-- At line 26:
/* Show the results (now only the 1 row that does not conflict with the CHECK constraint remains) */
SELECT * FROM Organizations;
-- Result: 1 rows returned in 3ms
-- At line 28:
/* Do not need the old table anymore */
DROP TABLE IF EXISTS Organizations_old;
-- Result: query executed successfully. Took 3ms
-- At line 30:
/* Finally clean up the demo environment */
DROP TABLE IF EXISTS Organizations_old;
-- Result: query executed successfully. Took 2ms
-- At line 32:
/* should not exist*/
DROP TABLE IF EXISTS Organizations;
-- Result: query executed successfully. Took 2ms

    As can be seen:-

    1. 2 rows were inserted to the table when it only had the single CHECK constraint on the email column. The row that conflicted with the email CHECK constraint was IGNOREd.
      1. As -- Result: 2 rows returned in 7ms
    2. Only 1 row is inserted after the table is effectively changed (i.e. the table was renamed so the new table can use the original name) as the row 'X','' is IGNOREd due to the conflict with the address CHECK constraint.
      1. As per -- Result: 1 rows returned in 3ms

    Not being that familiar with DB Browser I'm unsure if there is a way to specify that the use of OR IGNORE when DB Browser applies the change.

    An alternative approach that will retain the offending rows that are in conflict, would to to use an UPDATE to change the data e.g.

    UPDATE Organizations SET address = 'NOT PROVIDED' WHERE address NOT REGEXP '\S';

    The offending rows would then have NOT PROVIDED in the address column and then the DB Browser's modify table could then be used as the rows will then not conflict.

    Additional re comment

    I can't use flags in REGEXP with SQLite: REGEXP '(\S)' is ok, but REGEXP '/(\S)/i' is wrong.

    As per the comment in response, the modifier /i does not appear to be in the flavour of REGEXP used.

    Furthermore, if my understanding is correct, the result of /S or /s will not be changed by specifying either case, as whitespace has difference according to space (e.g. there is a space not an upper and/or lower case space).

    The following SQL (as used in DB Browser to obtain the results show later) has two purposes:-

    1. to show that using /i, or various ways to see if / should be otherwise be coded (see columns r5Case???), does not appear to work, and
    2. to show how you can go about testing REGEXP expressions

    The SQL being:-

    DROP TABLE IF EXISTS Organizations;
CREATE TABLE IF NOT EXISTS Organizations (email TEXT /*CHECK(email REGEXP '/S')*/, address TEXT);
DELETE FROM Organizations;
INSERT OR IGNORE INTO Organizations VALUES
    /* Values added to test the various REGEXP expressions used (columns r? )*/
    ('A','ABCDEFGHI'),
    ('A','ABCDEFGHI JKLMN'),
    ('A',' '),
    ('A',''),
    ('A',null),
    ('A','this is me saying hello'),
    ('A','American gray'),
    ('A','English grey'),
    ('A','ooops neither American or English groy')
;
SELECT * /* All non-hidden columns */,
    /* Plus derived/generated columns aliased using AS*/
    address REGEXP '\S' AS r1, /* */
    address REGEXP '(\S)' AS r2, /* Same as r1 */
    address REGEXP '.a' AS r3,
    address REGEXP '\s' AS r4,
    address REGEXP '^A' AS r5,
    address REGEXP '^[a|A]' AS r6,
    address REGEXP 'hello' AS r7,
    address REGEXP 'gray|grey' AS r8,
    address REGEXP '^A/i' AS r5Case001,
    address REGEXP '^A//i' AS r5Case002,
    address REGEXP '^A\i' AS r5Case003,
    address REGEXP '^A\\i' AS r5Case004
FROM Organizations;

    When run the result of the above will be:-

    result

    As can be seen the end result 0=false non zero (typically 1)= true shows that typical expressions work as expected BUT not the /i modifier (see r5Case??? columns).