Re: Unique Keys

Kenneth Downs wrote:
> Continuing the debate on unique keys. Can anything at all be
converted to a
> unique key? What about this one:
>
> CREATE TABLE reservations (
> hotel_room char(4),
> date_start date,
> date_end date )
>
> which requires a constraint that no two reservations can overlap. I
believe
> Tony gave this one to me. If so, Tony you'll be happy to know I've
got my
> own version of this to deal with as of last week.

I am indeed!

> It seems to me very obvious as to how to do this with a unique
constraint,
> and I do not mean to make another table and "run out" the range into
> explicit unique values (though for the case of hotels I still claim
that's
> the right way to do it).

I see no way to do it with a unique constraint, other than the "run out" method. Between this post and your next one, I can't make out whether you actually have a way to do it or whether you just have a strong feeling that it should be possible. With a unique constraint on the reservations table above, you have just the following possibilities (excluding permutations):

unique (hotel_room)
unique (hotel_room,date_start)
unique (hotel_room,date_end)
unique (hotel_room,date_start,date_end)
unique (date_start)
unique (date_end)
unique (date_start,date_end)

Apart from the first, which prevents a room from having more than one reservation at all, none will prevent the following invalid combination of inserts:

insert into reservations (hotel_room, date_start, date_end) values ('1234', DATE '2004-11-25', DATE '2004-11-27'); insert into reservations (hotel_room, date_start, date_end) values ('1234', DATE '2004-11-26', DATE '2004-11-29');

Mikito has given an answer that works, but as you say does not use a unique constraint (how could it?) But I think you are looking at this the wrong way round: a unique constraint isn't primitive, it is a shorthand for a constraint that could be more generally expressed as:

(select 0 from the_table t1, the_table t2 where t1.primary_key != t2.primary_key
and t1.unique_key = t2.unique_key)
={}

This more general syntax is a lot more powerful, as Mikito's example shows. Received on Thu Nov 25 2004 - 14:00:02 CET