[SOLVED] Adding constraint based on group by and having SQLITE3

Adding constraint based on group by and having SQLITE3

I'm setting up a table in sqlite3 (Python). The table that I created is called test and I need to add a constraint to this table where the number of unique entries can't be bigger than 3 for any given date. The primary keys are x,y, and valid from. Note, I have many valid dates but this is just for illustration.

con = sqlite3.connect(path2Database)
cursorObj = con.cursor()
query = """ CREATE TABLE IF NOT EXISTS TEST
      (x INTEGER NOT NULL,
       y INTEGER NOT NULL,
       Valid_from DATE NOT NULL,
       Valid_until DATE,
       value REAL,
       PRIMARY KEY (x, y, Valid_from) )"""    
cursorObj.execute(query)

cursorObj.execute("""INSERT OR REPLACE INTO TEST VALUES
                  (2, 3, '2023-02-28', '2023-03-04', 1),
                  (1, 1, '2023-02-28', '2023-03-05', 1), 
                  (4, 2, '2023-02-28', '2023-03-02', 1)
                  """)
con.commit()

I should not be able to insert another row with valid date '2023-02-28' because it only allows 3 values for any given date.

cursorObj.execute("""INSERT INTO TEST VALUES (4, 2, '2023-02-28', '2023-03-02', 1)""")

I tried to add the constraint below but I constantly get an operational error. What is the best way to solve this?

query = """ CREATE ASSERTION checkCount CHECK
        ( NOT EXISTS (
        SELECT COUNT(x) AS total
        FROM TEST
        GROUP BY Valid_from
        HAVING total > 3
        ))"""

Thanks

Solution

You can use a trigger for this:

CREATE TRIGGER LIMIT_VALID_FROM_CNT
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'We already have three rows of these')
    WHERE (SELECT COUNT(*) AS total
         FROM TEST t
         WHERE t.valid_From=NEW.valid_from)>=3;
END;

Would this help?