Adding constraint based on group by and having SQLITE3

Question

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

Answer 1

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?

Answer 2

An alternative to using a TRIGGER would be to utilise a CTE (Common Table Expressions) approach.

e.g. based upon

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.

WITH
    cte_values(x,y,valid_from,valid_until,value,chk) AS (
        SELECT 4,2,'2023-02-28','2023-04-02',1,
        (SELECT count(*) FROM test WHERE valid_from = '2023-02-28') /*<<<<< get the count for the CHK */ 
    )
INSERT OR REPLACE INTO test SELECT x,y,valid_from,valid_until,value 
FROM cte_values 
WHERE   chk < 3 
;

• the hard coded values could be parameterised and bound (i.e. substituted by a ? for each value) when calling a function/method that executes the SQL

However, the INSERT OR REPLACE, if required to REPLACE changed non-primary key values rather than skip. e.g. if you had VALUES (4, 2, '2023-02-28', '2023-12-31', 10) then the above could become:-

/* example 2 skip but not if to be REPLACEd */
WITH
    cte_values(x,y,valid_from,valid_until,value,chk) AS (
        SELECT 4,2,'2023-02-28','2023-12-31',10,
        (SELECT count(*) FROM test WHERE valid_from = '2023-02-28')
    )
INSERT OR REPLACE INTO test SELECT x,y,valid_from,valid_until,value 
FROM cte_values 
WHERE chk < 3 OR (x IN (SELECT x FROM test) AND y IN (SELECT y FROM test) AND valid_from IN (SELECT valid_from TEST))
;

• a difference between this and the answer that uses a TRIGGER Demo

The following demonstrates both of the above:-

/* Build the data as per the question */
DROP TABLE IF EXISTS test;
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) )
;
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)
;
/* RESULT 1 the original data */
SELECT * FROM test;
/* example 1 skip but never REPLACE if skippable */
WITH
    cte_values(x,y,valid_from,valid_until,value,chk) AS (
        SELECT 4,2,'2023-02-28','2023-03-02',1,
        (SELECT count(*) FROM test WHERE valid_from = '2023-02-28') /*<<<<< get the count for the CHK */ 
    )
INSERT OR REPLACE INTO test SELECT x,y,valid_from,valid_until,value 
FROM cte_values 
WHERE   chk < 3 
;
SELECT * FROM test;
/* example 2 skip but not if to be REPLACEd */
WITH
    cte_values(x,y,valid_from,valid_until,value,chk) AS (
        SELECT 4,2,'2023-02-28','2023-12-31',10,
        (SELECT count(*) FROM test WHERE valid_from = '2023-02-28')
    )
INSERT OR REPLACE INTO test SELECT x,y,valid_from,valid_until,value 
FROM cte_values 
WHERE chk < 3 OR (x IN (SELECT x FROM test) AND y IN (SELECT y FROM test) AND valid_from IN (SELECT valid_from TEST))
;
/* RESULT 3 replace instead of skip if the primary key matches */
SELECT * FROM test;

/* Cleanup the DEMO environment */
DROP TABLE IF EXISTS test;

The results:-

• 

  • The original data

• 

  • The data (unchanged/insert skipped) as 3 rows already exist for 2023-02-28

• 

  • this time the highlighted values have been changed i.e. the row has been REPLACEd