Re: Making a table with max 1 row

[John English]

On 10/10/2011 22:05, amfr...@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/
------------------------------------------------------------------------