Table with only one column or add a numeric primary key?

Question

Let's say I need a simple table with account id and no other information. There are two ways to do it:

id varchar(255) PRIMARY KEY

Or to add a numeric primary key:

id int PRIMARY KEY
accountId varchar(255) UNIQUE NOT NULL

What are the advantages / disadvantages of both approaches and which one would you choose and why?

What implications does the first solution has to maintainability (what if we need to change the id for a single row) and for performance?

Answer 1

This boils down to the surrogate key versus natural key debate in the database world. See for example here, here and here for texts on the topic. I think both choices are valid, but in this case I would choose the AccountID as a natural key (given that the AccountID is unique for each account, will not be null, and will not be subject to changes), because it means less overhead. In this case, I do not see added value to a surrogate key.

Natural keys:

• have meaning for the user
• are hard to change when needed
• may lead to needing less joins in queries

Surrogate keys:

• don't mean anything to the user
• are not subject to changes
• may lead to needing more joins in queries
• may require extra or larger indexes

Answer 2

The difference is that the PRIMARY KEY constraint implies/enforces a NOT NULL CONSTRAINT. In the first example the varchar(255) will be effectively promoted to varchar(255) NOT NULL

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE pk
        ( id varchar(255) PRIMARY KEY
        );

CREATE TABLE uniq
        ( id int PRIMARY KEY
        , accountid varchar(255) UNIQUE
        );

INSERT INTO pk (id) VALUES(NULL);
INSERT INTO uniq (id, accountid) VALUES(1, NULL);

Result:

DROP SCHEMA
CREATE SCHEMA
SET
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pk_pkey" for table "pk"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "uniq_pkey" for table "uniq"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "uniq_accountid_key" for table "uniq"
CREATE TABLE
ERROR:  null value in column "id" violates not-null constraint
INSERT 0 1

The first insert fails because of the PK (-->>NOT NULL) constraint; the second one succeeds.