Constrain total number of rows in check constraint

Question

I would like to create a table which can only contain a fixed number of rows at a time, for example just one:

create table test (
    someId INT not null,
    comment VARCHAR(4096) null,
    check((select count(*) from test) < 2)
)

But the given check-constraint is not allowed:

Error: Aggregates or subqueries are not allowed in check constraints.

SQLState: ZZZZZ ErrorCode: 7356

Is there an alternative approach?

Answer 1

You seem to want to create a table that is guaranteed to have at most one row. That seems like an unusual use for a table. Normally, I would recommend a trigger.

Because you only want one row, you can use this hack:

create table test (
    someId INT not null,
    comment VARCHAR(4096) null,
    guarantee_one_row int default 0,
    constraint test_only_one_row unique(guarantee_one_row)
);

It adds a column with a default value and then defines a unique constraint on it.

I should point out that you can do this with a computed column as well:

create table test (
    someId INT not null,
    comment VARCHAR(4096) null,
    guarantee_one_row as ('one row') materialized,
    constraint test_only_one_row unique(guarantee_one_row)
);