SQLite: Unique per title and date (only date without time)-CodePudding

I have a table in which i store some titles as a TEXT and a date also as a TEXT.

I am inserting the date value as a string in ISO format in the database.

Now i want to have a unique combination of title and date but only considering the date part(year, month and day) of the ISO string and ignoring the time part. So i want a unique title per specific day in the year.

Example:

INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_A', '2022-06-20T13:53:41.680Z') -- OK
INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_A', '2022-06-20T22:12:32.430Z') -- NOT OK same title and only time is different
INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_B', '2022-06-20T13:53:41.680Z') -- OK same date but title is different

What are my best options? I could implement a check but i'm not sure if that is the best option because as the table grows this could make the check slow.

CodePudding user response：

SQLite does not allow the use of functions inside the definition of a UNIQUE constraint, which would solve the problem like this:

UNIQUE(TITLE, date(DATE)) -- not allowed

Instead you can define a generated column (version 3.31.0 ) as the date part of the column DATE and use that in the definition of the constraint.

It would make more sense to rename your existing column to TIMESTAMP and name the generated column as DATE:

CREATE TABLE TABLE_A (
  TITLE TEXT, 
  TIMESTAMP TEXT,
  DATE TEXT GENERATED ALWAYS AS (date(TIMESTAMP)),
  UNIQUE(TITLE, DATE)
);

INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_A', '2022-06-20T13:53:41.680Z'); -- OK
INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_A', '2022-06-20T22:12:32.430Z'); -- error
INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_B', '2022-06-20T13:53:41.680Z'); -- OK

As it is, the generated column DATE is not actually stored in the table.

CodePudding user response：

You could maintain a pure date column and then impose a unique constraint on the combination of title and date.

CREATE TABLE TABLE_A (
    TITLE VARCHAR NOT NULL,
    DATE VARCHAR NOT NULL,
    TIMESTAMP VARCHAR NOT NULL,
    CONSTRAINT c_unique UNIQUE (TITLE, DATE)
);

INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_A', '2022-06-20', '2022-06-20T13:53:41.680Z') -- passes
INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_A', '2022-06-20', '2022-06-20T22:12:32.430Z') -- fails
INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_B', '2022-06-20', '2022-06-20T13:53:41.680Z') -- passes