db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Making a table with max 1 row
Date Thu, 13 Oct 2011 16:24:02 GMT
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/
------------------------------------------------------------------------

Mime
View raw message