Re: SQL in constraints

I'm working with mark and what we're really looking for is...

We have a table with a date range on it i.e. start and end dates. We want to make sure that for any particular person (EMPNO???) that the date ranges entered do not overlap those of any existing records.

How best to implement this?

We thought

1. constraint, but you can't put SQL in it.
2. database trigger. This would give us a mutating table though, we suspect as we need to query the table being inserted (or updated) into.
3. application (Forms) code. This will work but we'd like the extra safety net of testing at the server level also.

Any ideas anyone?

Alan

Kenny Gump wrote in message <36db0510.0_at_news.mountain.net>...
>No, a check constraint cannot contain a sub-query. A foreign key will
>enforce that name exists in another table.
>
>Kenny Gump
>OCP 7.3 DBA
>-------------------------
>mgumbs_at_hotmail.com wrote in message <7behli$p5n$1_at_nnrp1.dejanews.com>...
>>Is it possible to use SQL in constraints?
>>
>>e.g Create table football.....
>> ...
>> ...
>> Constraint rs_football_name
>> check name in (select name from register where....)
>>
>>If not, are there any alternatives? I have thoought of a pre-insert
>trigger
>>but is there anything else?
>>
>>Replies by e-mail if poss.
>>
>>Mark
>>
>>-----------== Posted via Deja News, The Discussion Network ==----------
>>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
Received on Tue Mar 02 1999 - 06:01:49 CST