Moinsen,

Nikolaus Kern schrieb am 18.05.2010 um 17:19:19 (+0200):

> Using now Firebird for some time I am facing now a question where I am
> simply stuck.
>
> *) There is a simple table to store the allocation of employees to
> specific projects
> *) Its stored with a start- and enddate
> *) The database should check now if the same employee is booked to the
> same or an overlapping timeframe

Note that your code doesn't cover the case of a full overlap, i.e. where
the new BAUSTELLE covers the entire period of another one. The solution
is simply to add the inverted version of your current check to itself.

> The code below works perfect until a BAUSTELLE (= project) is renamed.
> In this case the CHECK condition fires and seems to find itself.

You could probably solve this problem by adding another table holding
the BAUSTELLENNAME plus BAUSTELLENNUMMER (an ID), and referring to the
ID only, using either a FOREIGN KEY constraint, or a lookup, whatever
suits your case better.

> ***********************************************
> CREATE TABLE DETAILPLANUNG (
> BAUSTELLE CHAR(30) CHARACTER SET NONE NOT NULL COLLATE NONE,
> MITARBEITER INTEGER,
> STARTDATUM DATE,
> STOPDATUM DATE);
>
>
> ALTER TABLE DETAILPLANUNG ADD CONSTRAINT CHK_DETAILPLANUNG CHECK (EXISTS
> (SELECT
> *
> FROM DETAILPLANUNG
> WHERE MITARBEITER = NEW.MITARBEITER AND
> ((NEW.STARTDATUM BETWEEN STARTDATUM AND STOPDATUM) OR
> (NEW.STOPDATUM BETWEEN STARTDATUM AND STOPDATUM)) ));

You're checking for the existence of an overlap here. Isn't that
precisely what you want to exclude? Didn't you forget a NOT?

--
Michael Ludwig