How to allow only one row for a table?
postgresql, postgresql-9.1, sql
Solution
A `UNIQUE` constraint allows multiple rows with `null` values, because two `null` values are not considered to be the same. (Except when using `NULLS NOT DISTINCT` in Postgres 15 or later.)
Similar considerations apply to `CHECK` constraints. They allow the expression to be `true` or `null` (just not `false`). Again, `null` values get past the check.
To rule that out, define the column `NOT NULL`. Or make it the `PRIMARY KEY` since PK columns are defined `NOT NULL` automatically. See:
- Why can I create a table with PRIMARY KEY on a nullable column?
Also, just use `boolean`:
CREATE TABLE public.onerow (
onerow_id bool PRIMARY KEY DEFAULT true
, data text
, CONSTRAINT onerow_uni CHECK (onerow_id)
);
The `CHECK` constraint can be that simple for a `boolean` column. Only `true` is allowed.
You may want to `REVOKE` (or not `GRANT`) the `DELETE` and `TRUNCATE` privileges from `public` (and all other roles) to prevent the single row from ever being deleted. Like:
REVOKE DELETE, TRUNCATE ON public.onerow FROM public;
Problem
I have one table in which I would like only one entry. So if someone is trying to insert another row it shouldn't be allowed, only after someone deleted the previously existing row. How do I set a rule for a table like this?