> I suppose the whole idea of constraints between records (rather than
> just inside a single record) isn't all that well suited to multi-gen
> databases.
>
> They even had to 'cheat' to get PK's to work!!! ;)
>
> At least I know of this limitation now. I always wondered why people
> like M$ didn't use multi-gen. Now I know...

...

> I tested it with
> a simple integer field, that I wanted to be unique (without using a
> unique index!).

Do you think the problem lies in multi-versioning ?
Tell me how would you ensure the field value is unique without using
unique index/constraint in M$ ?
Or how would you implement your overlap constraint in M$ ?


Ivan

----- Original Message -----
From: "johnsparrowuk" <jsparrow@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, June 14, 2004 2:54 PM
Subject: [firebird-support] Re: Checking periods don't overlap


> I'm glad it's not just me! I assume the only answer is to use a high
> degree of locking? Like table_stability or "select * from T with
> lock". Ouch.
>
> Of course you then loose all multi-gen advantages!
>
> Otherwise you just have to *hope* nothing sneaks through the trigger
> below, and to be properly sure, each time you use the table, scan it
> again in your new transaction context to ensure nothing invalid
> exists.
>
> I suppose the whole idea of constraints between records (rather than
> just inside a single record) isn't all that well suited to multi-gen
> databases.
>
> They even had to 'cheat' to get PK's to work!!! ;)
>
> At least I know of this limitation now. I always wondered why people
> like M$ didn't use multi-gen. Now I know...
>
> John


----- Original Message -----
From: "johnsparrowuk" <jsparrow@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, June 14, 2004 8:12 PM
Subject: [firebird-support] Re: Checking periods don't overlap


> Thanks Martijn,
>
> Didn't know you could do that! But it doesn't help. I tested it with
> a simple integer field, that I wanted to be unique (without using a
> unique index!).
>
> ALTER TABLE T ADD CONSTRAINT CHK_T check (not exists (select * from
> T where intfield = new.intfield));
>
> But it only looks in the current transaction, doesn't dirty-read
> like an index does.
>
> So it's no better than using a trigger :(
>
> John