Making a table with max 1 row

[Am...@web.de]

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

Thanks for any suggestions / answers

Biene Maja

Re: Making a table with max 1 row

[John English <jo...@gmail.com>]

On 10/10/2011 22:05, AmFreak@web.de wrote:
>
> 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).
>
> Thanks for any suggestions / answers

Use a trigger:

CREATE TRIGGER remove_test
   AFTER INSERT ON test
   REFERENCING NEW AS ins
   FOR EACH ROW MODE DB2SQL
   DELETE FROM test
     WHERE field1=ins.field1
     AND (SELECT COUNT(*) FROM test) > 1;

This will delete the inserted row if there is more than one row in the
table.

HTH,

------------------------------------------------------------------------
  John English | My old University of Brighton home page is still here:
               | http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------

Re: Making a table with max 1 row

["Biene_Majo@yahoo.de" <Bi...@yahoo.de>]

Sorry for the late answer, just wanted to thank you 2 for the suggestions,  
guess i will go with John's answer.

Greetings

Biene Maja

Re: Making a table with max 1 row

[Knut Anders Hatlen <kn...@oracle.com>]

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