db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Making a table with max 1 row
Date Tue, 11 Oct 2011 10:29:11 GMT
AmFreak@web.de 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

Mime
View raw message