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?
Advertisement
Answer
A UNIQUE
constraint allows multiple rows with NULL
values, because two NULL
values are never considered to be the same.
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, the column must be defined NOT NULL
. Or make it the PRIMARY KEY
since PK columns are defined NOT NULL
automatically. Details:
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;