-1

When create two tables using the following queries in PostgreSQL:

 create table test_unique_pk (
    id serial primary key,
    value varchar not null
 );

 create table refer_unique_pk (
    id integer,
    value varchar,
    foreign key (id, value) references test_unique_pk(id, value)
 );

I got 

there is no unique constraint matching given keys for referenced table "test_unique_pk".

If I modify the first table to

 create table test_unique_pk (
     id serial primary key,
     value varchar not null,
     unique(id, value)
 );

It just works fine.

However since primary key is already unique, in my opinion, the composite (id, value) should also be unique, since we can not construct two tuples with same id, and thus we can not construct two equal tuples of (id, value).

If the above statement is correct, why PostgreSQL does not automatically add a unique constraint on composite references including primary key?

xiang0x48
  • 621
  • 6
  • 20
  • 1
    Why would you want to add the `value` column as part of the foreign key to begin with if `id` is already unique`? –  Apr 23 '19 at 07:42
  • Because that's the way they defined the language. This question cannot be answered without asking the designers. If you just want confirmation that a superset of a unique column set is unique, edit your question to ask that, and you are correct. – philipxy Apr 23 '19 at 07:56
  • @a_horse_with_no_name, I need to keep the table `test_unique_pk` and the table `refer_unique_pk` have the same `id` and `value`. thus the database should reject rows like `test_unique_pk(id = 3, value="test"), refer_unique_pk(id = 3, value="other")`. – xiang0x48 Apr 23 '19 at 07:59
  • To avoid duplication the `value` column should not be present in one of the `refer_unique_pk` or `test_unique_pk` tables. The `id` column is enough to ensure uniquness so if you were to need the value it should be fetched via a join. – Lucas Apr 23 '19 at 11:58

1 Answers1

0

The definiton of a Primary Key is to be unique and not null at the same time. A NULL value is always like an "unknow" value, so you can't use this insite a PK.

https://www.w3schools.com/sql/sql_primarykey.asp

Fabio Telles Rodriguez
  • 56
  • 2