title: PostgreSQL - Why can't I create composite foreign key based on unique column without declaring composite key as unique?
body: <p>I've noticed that you cannot create a foreign key if the referenced key isn't unique, but, if I have records <code>(x, y, z)</code> where <code>x</code> is unique, it is "intuitive" to assume that every record will always be unique.</p>
  
  <p>So, is it there any particular reason I have not considered for why I can't do something like this</p>
  
  <pre><code>create table x(
      id int primary key,
      something int not null
  );
  create table y(
      id serial primary key, -- whatever, this doesn't matter
      x_id int not null,
      x_something int not null,
      foreign key (x_id, x_something)
          references x(id, something)
  );
  </code></pre>
  
  <p>in Postgres which throws</p>
  
  <pre><code>ERROR:  there is no unique constraint matching given keys for referenced table "x"
  </code></pre>
  
  <p>and which can be corrected adding <code>unique (id, something)</code> in table <code>x</code>.</p>
  
  <p>Is this behavior just present in Postgres, or is something defined in the SQL standard?</p>
  
  <p>Is there any way to reference the composite key without requiring the <code>unique</code> constraint?</p>
  
  <p><strong>EDIT 1:</strong>
  Here is an example of a situation in which this would be useful</p>
  
  <pre><code>create table movie_reservation(
      id serial primary key,
      movie_id int references(...),
      -- ... (reservation data like the time and interval),
      seen boolean not null default false -- wether a user has seen it
  );
  -- want califications of moves that HAVE BEEN SEEN
  create table movie_calification(
      movie_reservation_id int not null,
      seen boolean
        not null
        check (boolean = true),
      stars smallint
          not null
          check (stars between 1 and 5),
  
  
      foreign key (movie_reservation_id, seen)
          references movie_reservation(id, seen)
  );
  </code></pre>