Paul Hartley <phartley@gmail.com> writes:
> ... I'm unclear
> if PostgreSQL treats primary keys differently from unique, non-null
> constraints.
The *only* thing that the system does specially with a primary key
constraint is that a PK creates a default column target for foreign key
references. For example,
create table m (id int primary key);
create table s (refid int references m);
versus
create table m (id int);
create unique index mi on m (id);
create table s (refid int references m(id));
I have to spell out "(id)" in that last command because there's no PK
to establish a default target.
Other than that, behavior and performance should be the same. The
planner and executor only care about the indexes, not about whatever
constraints they might have come from. Likewise, NOT NULL is NOT NULL
regardless of what syntax you used to slap it onto the column.
regards, tom lane
