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