How to allow only one row for a table?

Question

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?

Answer 1

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:

• 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; 

Answer 2

Add a new column to the table, then add a check constraint and a uniqueness constraint on this column. For example:

CREATE TABLE logging (     LogId integer UNIQUE default(1),     MyData text,     OtherStuff numeric,     Constraint CHK_Logging_singlerow CHECK (LogId = 1) ); 

Now you can only ever have one row with a LogId = 1. If you try to add a new row it will either break the uniqueness or check constraint.

(I might have messed up the syntax, but it gives you an idea?)