I have a table that records observed movies in a cinema:
CREATE TABLE cinema_movie_name (
id integer DEFAULT nextval('cinema_movie_name_id_seq'::regclass) PRIMARY KEY,
cinema_id integer NOT NULL REFERENCES cinema(id) ON DELETE CASCADE ON UPDATE RESTRICT,
name text NOT NULL,
movie_id integer REFERENCES movie(id) ON DELETE SET NULL ON UPDATE RESTRICT,
created_at timestamp with time zone NOT NULL DEFAULT now()
);
Every time we see a new movie name in the cinema, we record it in this table and map it to movie_id
in our database.
The current uniqueness is enforced on (cinema_id, name)
, i.e.
CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx ON cinema_movie_name(cinema_id, name);
The problem is that over a long time, there can be multiple different movies with the same name, i.e. after some arbitrary time duration (id, cinema_id, name, movie_id)
becomes invalid.
Therefore, I need to create an index that enforces a unique constraint on (cinema_id, name)
and created_at
±3 month.
How to create a unique index with a temporal column?