How to allow only one row for a table?

Question

I have one table in which I would like to have only one record . Therefore, if someone is trying to insert another row, this should not be allowed, only after someone has deleted a pre-existing row.

How to set a rule for a table as follows?

Answer 1

A UNIQUE constraint allows multiple strings with NULL values, since two NULL values ​​are never considered the same.

Similar considerations apply to CHECK restrictions. They allow the expression to be TRUE or NULL (just not FALSE ). Again, NULL values ​​are tested.

To eliminate this, the column must be defined must NOT NULL . Or do it PRIMARY KEY , since PK columns are NOT NULL automatically. Details:

• Why can I create a table with PRIMARY KEY in a column with a null value?

Alternatively, just use boolean :

 CREATE TABLE onerow ( onerow_id bool PRIMARY KEY DEFAULT TRUE , data text , CONSTRAINT onerow_uni CHECK (onerow_id) ); 

A CHECK constraint can be so simple for a boolean column. Only TRUE allowed.

Answer 2

Add a new column to the table, then add a validation constraint and a uniqueness constraint to this column. For instance:

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

Now you can have only one line with LogId = 1. If you try to add a new line, it will either violate uniqueness or check the restriction.

(Maybe I messed up the syntax, but does this give you an idea?)

Answer 3

You must create an ON BEFORE INSERT trigger in this table. In the trigger, call a procedure that checks count(*) , and when the counter is 1, it returns an error message to the user, otherwise the insert may continue.

Check this documentation for an example.