Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of john.foreign@gmail.com
 designates 209.85.215.172 as permitted sender)
Message-ID: <4E9710A2.8080905@gmail.com>
Date: Thu, 13 Oct 2011 18:24:02 +0200
From: John English <john.foreign@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Making a table with max 1 row
References: <op.v25je2h5zm6rri@pawel-pc>
In-Reply-To: <op.v25je2h5zm6rri@pawel-pc>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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/
------------------------------------------------------------------------