'How to allow only one row for a table?

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?

sqlpostgresqlpostgresql-9.1


Solution 1:[1]

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?)

Solution 2:[2]

You should create a ON BEFORE INSERT trigger on this table. On the trigger, call a procedure that checks count(*) and when the count is 1, it returns an exception message to the user otherwise the insert is allowed to proceed.

Check this documentation for an example.

Solution 3:[3]

I suppose no additional field is requried here. Just unique index on constant:

CREATE UNIQUE INDEX ux_onerow ON onerow ((0));

Solution 4:[4]

You can force single row with:

id int GENERATED ALWAYS AS (1) STORED UNIQUE

This will force the id column to always be 1 and since it's a UNIQUE column the table will only be able to hold this row.

DB Fiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ben
Solution 2
Solution 3 barsuk
Solution 4 enriquejr99

Related Questions