Despite not a being common pattern, I’ve ran into situations where I needed to use a sort of singleton pattern in the database.

Usually the case is when one needs to store a single unique value or when one needs to single out exactly one record among multiple.

For demo purposes, let’s consider we have a users table and want one user to be the “boss”. And there can only be one boss, of course.

PostgreSQL

In PostgreSQL this can be achieved by having a boss table, which will have a single column and row (or none), referencing the id in the users table.

To prevent this table from having more rows, we create the boss_singleton index which evaluates the expression (true). Meaning, when an insert happens, the database will evaluate the index expression, which will be true if a row exists.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name TEXT
);

CREATE TABLE boss (
  user_id INT PRIMARY KEY REFERENCES users
);

CREATE UNIQUE INDEX boss_singleton ON boss ((true));

-- test data
INSERT INTO users (id, name) VALUES (1, 'Morty'), (2, 'Rick');
INSERT INTO boss (user_id) VALUES (1);
INSERT INTO boss (user_id) VALUES (2) ON CONFLICT DO NOTHING;

-- the second insert should do nothing and user_id should equal 1
SELECT (user_id = 1) AS worked FROM boss;

MySQL

MySQL does not allow us to use expressions. The equivalent solution is quite more hacky (personally, I would think thrice before doing this).

Here we need a “dummy” column (unique_value) that will hold… a unique value! The column needs to be restricted by a single value enum. Also, this will be the index column.

So, after inserting for the first time, unique_value holds 'lockme'. Any other insert has to obey the enum constraint but since there is already a 'lockme' value in that column, it fails.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name TEXT
);

CREATE TABLE boss (
  user_id INT PRIMARY KEY REFERENCES users,
  unique_value ENUM('lockme') NOT NULL DEFAULT 'lockme'
);

CREATE UNIQUE INDEX boss_singleton ON boss (unique_value);

-- test data
INSERT INTO users (id, name) VALUES (1, 'Morty'), (2, 'Rick');
INSERT INTO boss (user_id) VALUES (1);
INSERT INTO boss (user_id) VALUES (2) ON DUPLICATE KEY UPDATE user_id = user_id;

-- the second insert should do nothing and user_id should equal 1
SELECT (user_id = 1) AS worked FROM boss;

Bonus

About the syntatic difference that leads to the distinct solutions.

In PostgreSQL, note ( { column_name | ( expression ) }.

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } 
    ...

Versus MySQL’s key_part definition.

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]
...