A table with a single column or adding a numeric primary key?

Question

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

id varchar(255) PRIMARY KEY 

Or 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 ones would you choose and why?

What are the implications of the first solution for maintainability (what if we need to change the identifier for one row) and for performance?

Answer 1

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

Natural keys:

• matter to the user
• difficult to change if necessary
• may result in fewer connections in queries

Surrogate keys:

• means nothing to the user
• not subject to change
• may lead to the need for joining queries
• additional or larger indexes may be required

Answer 2

The difference is that the PRIMARY KEY constraint implies / applies NOT NULL CONSTRAINT. In the first example, varchar(255) will effectively advance 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 insertion fails due to a PK constraint (- → NOT NULL); second successful.

Answer 3

if the contents of this column is unique (which seems to relate to identifiers), then go ahead and make it the primary key, otherwise create another numeric column as the primary key.

Yours faithfully,