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


--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@u...> wrote:

> Hi John,
>
> > Can a check constraint see other records? I thought they could

only

> > see the fields in the current record?
>
> There are two types of CHECK constraints --
>
> 1) column check constraints
> eg: MYVAL INTEGER CHECK (MYVAL > 1)
>
> 2) table check constraints
> Can operate on multiple columns
> (MYVAL > 1 AND MYCOL = 0)
>
> or can have complex SELECTs etc etc...
>
> With regards,
>
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL

& MS SQL

> Server.
> Upscene Productions
> http://www.upscene.com
>
>
> > --- In firebird-support@yahoogroups.com, "Martijn Tonies"
> > <m.tonies@u...> wrote:
> > > Hi John,
> > >
> > >
> > > > You don't *need* to use a trigger, but using your solution

has

> > the
> > > > same problem - if there is an uncommitted record in another
> > > > transaction (with an overlapping time-period), you won't see

it,

> > and
> > > > it won't see you.
> > > >
> > > > You'll both commit fine. And then you've got a problem.
> > >
> > > How about using a CHECK constraint? Doesn't that solve
> > > your problem?
> > >
> > > With regards,
> > >
> > > Martijn Tonies
> > > Database Workbench - developer tool for InterBase, Firebird,

MySQL

> > & MS SQL
> > > Server.
> > > Upscene Productions
> > > http://www.upscene.com
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >