SQLite Forum

column constraint ascending primary key with text type
Login

column constraint ascending primary key with text type

(1) By anonymous on 2022-01-24 06:53:34 [link] [source]

  • is it possible to create a column constraint of type text which ensures that subsequent values are ascending?

    • CREATE TABLE calendar(date TEXT PRIMARY KEY ASC NOT NULL) does not work.
    • abusing lag function does not work either:

      CREATE TABLE calendar(date TEXT PRIMARY KEY NOT NULL CHECK (lag(DATE(date),1)<DATE(date)))

INSERT INTO calendar (date) VALUES ('2022-12-13')

  • now i should be prevented from inserting any value < 2022-12-13

  • i guess the questions boils down to, how to write a constrain which gets a previous column value

(2) By Larry Brasfield (larrybr) on 2022-01-24 07:21:46 in reply to 1 [link] [source]

The doc on CHECK constraints, (last sentence of first paragraph), says "The expression of a CHECK constraint may not contain a subquery." Without a subquery, there is no "previous column value" (which I take to mean the value of the same column in a "previous" row.)

Others may have words on how to think about ordering and its enforcement. It's a dubious concept, but beyond me this close to bedtime.

(3) By Gunter Hick (gunter_hick) on 2022-01-24 08:32:02 in reply to 1 [link] [source]

Maybe you are looking for a trigger program. Something like

SELECT RAISE(IGNORE,'ascending dates are required') FROM calender WHERE NEW.date <= (SELECT MAX(date) from calender));

Note that the concept of "previous row" makes no sense for a set, and that having an index on date is required to avoid full table scans on INSERT.

(4) By Simon Slavin (slavin) on 2022-01-24 10:50:58 in reply to 3 [link] [source]

It can't be done with two TRIGGERs. The method depends on another TABLE (let's call it 'Config') having a fixed row to keep track of 'last date recorded'.

A TRIGGER AFTER INSERT takes a copy of the new value and writes it to a fixed row of another TABLE. A TRIGGER BEFORE INSERT looks up the current value in the other TABLE and makes the INSERT fail if the new value is too low.

However, this depends on the TABLE you're inserting new rows into never having rows UPDATEd or DELETEd. It also means that your program would have to have an initialisation routine to create the Config table and the appropriate row in it.

(5) By Keith Medcalf (kmedcalf) on 2022-01-24 16:29:56 in reply to 1 [link] [source]

How about:

sqlite> CREATE TABLE calendar(date TEXT PRIMARY KEY ASC NOT NULL);
sqlite> create trigger balderdash before insert on calendar
   ...> begin
   ...>   select raise(ABORT, 'date out of sequence')
   ...>     from calendar
   ...>    where new.date <= (select max(date) from calendar)
   ...>       or date > datetime('now');
   ...> end;
sqlite> insert into calendar values (datetime());
sqlite> insert into calendar values (datetime());
sqlite> insert into calendar values (datetime('now', '-1 day'));
Error: stepping, date out of sequence (19)
sqlite>

(6) By Keith Medcalf (kmedcalf) on 2022-01-24 16:37:45 in reply to 5 [link] [source]

You could even have your trigger check that the date is valid:

create trigger balderdash before insert on calendar
begin
   select raise(ABORT, 'date format is invalid')
    where datetime(new.date) IS NOT new.date;
   select raise(ABORT, 'date out of sequence')
    where new.date <= (select max(date) from calendar)
       or new.date > datetime('now');
end;

So that not only can you not insert out of sequence but you cannot insert garbage either ...

You cannot do this with a check constraint. A check constraint can only see the current row and is intended to express record/row/tuple invariants.

(7) By anonymous on 2022-01-25 04:00:17 in reply to 5 [source]

nice, exactly what i was looking for, triggers it is then. thanks a lot