CODEWITHSUNDEEP
mysqldatabasedatabase-designschema
Richard Knop
Richard Knop

Reputation: 83697

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

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?

Upvotes: 10

Views: 7346

Answers (3)

Josien
Josien

Reputation: 13867

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

Upvotes: 15

wildplasser
wildplasser

Reputation: 44240

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.

Upvotes: 2

CME64
CME64

Reputation: 1672

if the contents of that column are unique (which seems to be the case of IDs), then go ahead and make it the primary key, otherwise create another numeric column as a primary key.

regards,

Upvotes: 1

Related Questions