How are composite keys evaluated?

Question

Are composite keys guaranteed to be unique as long as the individual values of the columns it consists of are unique (as in the column values are separately evaluated), or is it the resulting value (as in a concatenation of the column values) that makes up the key and has to be unique?

Would, for example, the following two rows result in the same key, or would they both be considered unique and therefore allowed:

PRIMARY KEY (user_id, friend_id)

|-----------|-------------|
|  user_id  |  friend_id  |
|-----------|-------------|
|    10     |     283     |
|   1028    |      3      |
|-----------|-------------|

Now, I'm obviously no database expert, it's actually the first time I'm thinking about using composite keys (never had reason to before), so it might be something that "everyone" knows about or is just really easy to find in the documentation, but I've been unable to find an answer to it.

You expect the example above to work (logically, why shouldn't it? The separate values are certainly unique), but I just really want to be sure before I proceed with my project.

I am not sure, but I think it's pretty obvious that you cannot tell a composite PK is unique if the concatenation of its columns is unique :-) because this is not the concept of a composite PK. Now, you could argue that some INDEX (to make queries faster) could use the concatenation of the composite PK columns to calculate some hash (in this case, even this hash wouldn't be unique, of course, although good hash functions try to spread its values as much as possible to keep the search faster). — Leo, Feb 19 '14 at 19:25
Now, if you want to speed your composite PK check using INDEXES, then your question about PostgreSQL composite PK evaluation internals could fit here :-) IMO — Leo, Feb 19 '14 at 19:28

score 3 · Accepted Answer · edited May 23 '17 at 10:26

A PRIMARY KEY constraint is implemented by UNIQUE index on the involved columns plus NOT NULL constraints on all involved columns.

"Unique" means that the combination of all columns is unique. What you are worrying about is the concatenation of the textual representation of two values ('10' || '283') = ('1028' || '3') but that's not how composite types operate at all. All fields are considered separately and as values of the defined data types, not as text representations.

NULL values are never considered to be equal, but those are not allowed in pk columns.

The order of columns is relevant for performance. The accompanying composite index preferences the leading columns. More details in this closely related answer:
PostgreSQL composite primary key

Great. Thanks! That's how I thought and hoped it would work, but I haven't been able to find an answer to it before so just had to be sure. And thank you for the link, as well. Cleared things up. — Tom, Feb 19 '14 at 20:45

score 1 · Answer 2 · answered Feb 19 '14 at 19:20

each unique constraint (including primary key constraints) demands that each row in the relation has a functional dependency on the projection to the attributes named in the constraint.

What that means is that, in your example,

user_id | friend_id
======= | =========
1       | 1
1       | 2
2       | 1
4       | 5

are all allowed; since no pair of <user_id, friend_id> occurs more than once. given the above, you could not have another <1, 1> because it would conflict with the first row.

How are composite keys evaluated?

2 Answers2