[email protected] writes:
> Hi,
>
> i need a table with 1 or 0 rows in it. So that after 1 row is inserted
> the table can only be updated or the row deleted, but there can never
> be a row inserted.
> I came as far as this:
>
> CREATE TABLE test
> (field1 INTEGER NOT NULL
> CHECK (field1 = 1) UNIQUE)
>
> but that would (obviously) mean that field1 could only have the value 1.
>
> I would need something like check(COUNT(*) FROM test <= 1).
Perhaps you could use a generated column that always has the same value,
and then add a unique constraint on that column. That would allow you to
insert one row, update and delete that row, but never insert more than
one row.
ij> create table test (
field1 int not null,
field2 boolean generated always as (true) unique not null);
0 rows inserted/updated/deleted
ij> insert into test(field1) values 1;
1 row inserted/updated/deleted
ij> insert into test(field1) values 2;
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'SQL111011121443950' defined on 'TEST'.
ij> update test set field1 = 99;
1 row inserted/updated/deleted
ij> delete from test;
1 row inserted/updated/deleted
ij> insert into test(field1) values 2;
1 row inserted/updated/deleted
ij> insert into test(field1) values 3;
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'SQL111011121443950' defined on 'TEST'.
--
Knut Anders
