I have a table representing movies. The fields are:
id (PK), title, genre, runtime, released_in, tags, origin, downloads.
My database cannot be polluted by duplicated rows, so I want to enforce uniqueness. The problem is that different movies could have the same title, or even the same fields except
downloads. How to enforce uniqueness?
I thought of two ways:
- make all the fields except
downloadsprimary key. I'm keeping
downloadsout since it's JSON and it will probably impact the performance.
- keep only
idas primary key, but add a unique constraint with all the other columns (except, again,
I read this question which is very similar, but I didn't quite understand what should I do. Currently this table is not related to any other tables, but in the future could be.
At the moment I have slightly less than 20,000 records, but I expect the number to grow. I don't know if this is somewhat relevant to the issue.
EDIT: I modified the schema and here is how I would create the table:
CREATE TABLE movies ( id serial PRIMARY KEY, title text NOT NULL, runtime smallint NOT NULL CHECK (runtime >= 0), released_in smallint NOT NULL CHECK (released_in > 0), genres text NOT NULL default ARRAY::text, tags text NOT NULL default ARRAY::text, origin text NOT NULL default ARRAY::text, downloads json NOT NULL, inserted_at timestamp NOT NULL default current_timestamp, CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin) );
I also added the
timestamp column, but that is not a problem as I won't touch it. So it will always be automatic and unique.