Is it reasonable to mark all columns but one as primary key?

Question

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 tags and downloads. How to enforce uniqueness?

I thought of two ways:

• make all the fields except downloads primary key. I'm keeping downloads out since it's JSON and it will probably impact the performance.
• keep only id as primary key, but add a unique constraint with all the other columns (except, again, downloads).

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.

Answer 1

Your table definition looks reasonable all over now. With all columns NOT NULL the UNIQUE constraint will work as expected - except for typos and minor differences in spelling, which may be rather common I am afraid. Consider @a_horse's comment.

Alternative with functional unique index

The other option would be a functional unique index (similar to what @Dave commented). But I would use a uuid data type to optimize index size and performance.

The cast from array to text is not IMMUTABLE (due to its generic implementation):

• Indexing an array for full text search

Hence you need a little helper function to declare it immutable:

CREATE OR REPLACE FUNCTION f_movie_uuid(_title text
                                      , _runtime int2
                                      , _released_in int2
                                      , _genres text[]
                                      , _tags text[]
                                      , _origin text[])
  RETURNS uuid LANGUAGE sql IMMUTABLE AS  -- faking IMMUTABLE
'SELECT md5(_title || _runtime::text || _released_in::text
         || _genres::text || _tags::text || _origin::text)::uuid';

Use it for the index definition:

CREATE UNIQUE INDEX movies_uni_idx
ON movies (f_movie_uuid(title,runtime,released_in,genres,tags,origin));

SQL Fiddle.

More Details:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
• Convert hex in text representation to decimal number

You might use the generated UUID as PK, but I would still use the serial column with its 4 bytes, which is simple and cheap for FK references and other purposes. A UUID would be a great option for distributed systems that need to generate PK values independently. Or for very huge tables, but there aren't nearly enough movies in our solar system for that.

Pros and Cons

A unique constraint is implemented with a unique index on the involved columns. Put relevant columns in the constraint definition first and you have a useful index for other purposes as collateral benefit.

There are other specific benefits, here is a list:

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

The functional unique index is (potentially much) smaller in size, which can make it substantially faster. If your columns are not too big, the difference won't be much. There is also the small overhead cost for the calculation.

Concatenating all columns can introduce false positives ('foo ' || 'bar' = 'foob ' || 'ar', but that seems very unlikely for this case. Typos are so much more likely that you can safely ignore it here.

Uniqueness and arrays

Arrays would have to be sorted consistently to make sense in any unique arrangement relying on the = operator because '{1,2}' <> '{2,1}'. I suggest look-up tables for genre, tag and origin with serial PK and unique entries, which allow fuzzy search for array elements. Then:

• either implement fully normalized n:m relationships that also provide referential integrity. Uniqueness of each set of references is harder to establish, you could use a MATERIALIZE VIEW (MV) with aggregated arrays as stepping stone.

  • How to implement a many-to-many relationship in PostgreSQL?

• or operate with sorted arrays of FK references (which cannot yet be supported with FK constraints). Tools from the additional module intarray may come in handy:

  • Compare arrays for equality, ignoring order of elements

Either way, working with arrays directly or with a normalized schema and a materialized view, searching can be very efficient with the right index and operators:

• Can PostgreSQL index array columns?

Aside

If you are using Postgres 9.4 or later consider jsonb instead of json.

Answer 2

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?"

What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the star's names? Some combination of two or more?

The answer to my question and yours are the same.

However, I would not think that genre would be a good candidate. One reason, genre is much too subjective a criteria. Is 'The Three Musketeers' action? drama? adventure? comedy? action-adventure? romantic-comedy? I often see the same movie listed under different genres. Even when you allow for multiple genres, your user may select an entirely different one not listed with the actual movie they are looking for.

Even runtimes can differ, especially between theater and VCR/DVD/b-ray versions.

So you need hard, objective attributes which will not change from one media release to another. Unfortunately, that can exclude the name of the movie as movies have been known to be renamed, especially after the release of a sequel.

What about release date? The theatrical release of 1993? The VCR release of 1999? The DVD release of 2004? You get the idea.

Come to think about it, what of all those movies directed by Alan Smithee? Has the real director ever finally stepped forward to put his name on the project after the fact? I don't know.

Hmm, I'd better stop while there are still some criteria left.

Some additional points:

• Yes, keep the surrogate key and create a unique index on the natural key fields (if you can finally nail those down). The surrogate key is best for foreign key references. You don't want to duplicate all the natural key fields in every table that contains a reference to a movie.
• Drop the array fields (genres, tags, origins). Go ahead and properly normalize those attributes. I have never seen an array field that was not a great deal more trouble than it was worth, especially if you want them to be searchable ("...where genre = 'horror'..."). Note this will not automatically eliminate any issues with case differences and spelling ( "Science Fiction" vs "SciFi" ) -- unless you properly maintain the lookup tables. But it is a whole lot easier to check for such differences in one field of a small table than every array cell of every row of a large table.

Answer 3

The ID column has no advantage at all when it comes to the uniqueness you want/need to enforce. Uniqueness of whatever combination of attributes is never going to be enforced by adding a meaningless ID. Its "advantage" only shows when you ever get to the point where you'd need a new table that needs a foreign key to this one. In that case, and IF you have included the Id, then you can use that one as the FK in your new table. (But don't think it will be a free lunch. The downside of such an approach is you'll likely find yourself writing more joins for the mere purpose of fetching information that could perfectly well have been part of that new table you made.)