SQLite Forum

Is there a way to get these constraints
Login

Is there a way to get these constraints

(1) By Cecil (CecilWesterhof) on 2020-12-06 16:38:41 [source]

I created a table with triggers. And it does mostly what I want. But I would like to have two constraints, but it seems it is not possible.

I have the following table:
CREATE TABLE results (
    resultID    INTEGER PRIMARY KEY AUTOINCREMENT,
    dateStr     TEXT    NOT NULL,
    weekNr      INTEGER, -- NOT NULL
    result      INTEGER NOT NULL,

    CONSTRAINT  date    CHECK(dateStr = date(strftime('%s', dateStr), 'unixepoch')),
    -- CONSTRAINT  weekNr  CHECK(TYPEOF(weekNr)  = 'integer')
    CONSTRAINT  result  CHECK(TYPEOF(result)  = 'integer')
)

And the following triggers:
CREATE TRIGGER resultsInsert AFTER INSERT ON results BEGIN
    UPDATE results
    SET    weekNr = STRFTIME('%W', results.dateStr)
    WHERE  rowId  = NEW.RowId;
END

CREATE TRIGGER resultsUpdateDateStr AFTER UPDATE OF dateStr, weekNr ON results BEGIN
    UPDATE results
    SET    weekNr = STRFTIME('%W', results.dateStr)
    WHERE  rowId  = RowId;
END


I would like to have weekNr NOT NULL and the constraint on weekNr enabled. And only supply dateStr and result, not weekNr.


While writing this post I had a few ideas. I can enable the NOT NULL part if I also set the DEFAULT on -1.
It is not ideal, but acceptable.

But I cannot enable the constraint on weekNr. The default -1 is an integer as is the generated weekNr, but when enabled I keep getting:
    Error: CHECK constraint failed: weekNr

Can this be solved?

I am using 3.24.0.

(2) By Bill Wade (wwade) on 2020-12-11 12:25:05 in reply to 1 [link] [source]

The strftime result is text, even if it looks like an integer.

sqlite> select strftime('%W', '2020-12-06 16:38:41');

48

sqlite> select typeof(strftime('%W', '2020-12-06 16:38:41'));

text

sqlite> select cast(strftime('%W', '2020-12-06 16:38:41') as integer);

48

sqlite> select typeof(cast(strftime('%W', '2020-12-06 16:38:41') as integer));

integer