Hello Svein, Michael,

Thanks for your infos.

I have inserted an autoincrement rowID and modified the CHECK as follows:

NOT EXISTS(
SELECT BAUSTELLE FROM DETAILPLANUNG
WHERE MITARBEITER = NEW.MITARBEITER AND ROWID <> NEW.ROWID AND
((NEW.STARTDATUM BETWEEN STARTDATUM AND STOPDATUM) OR
(NEW.STOPDATUM BETWEEN STARTDATUM AND STOPDATUM) OR
(STARTDATUM >= NEW.STARTDATUM AND STOPDATUM <= NEW.STOPDATUM) OR
(STARTDATUM = NEW.STOPDATUM AND STOPDATUM = NEW.STOPDATUM))
)

It works fine.

Thanks for you help !

Niko

Am 18.05.2010 21:22, schrieb Michael Ludwig:

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

[Non-text portions of this message have been removed]