Q: How do I avoid overlapping values?

Greetings,

Does anyone know a good way to solve the following problem, using Oracle 8.1.5?

I have a table called foo, with 3 columns, dateFrom, dateTo and price, looks something like this:

	create table foo (
		dateFrom date,
		dateTo	 date,
		price	 number
	);

The table contains multiple rows, and what I need to avoid is that 2 rows overlap.
	dateFrom	dateTo		price
Row 1:	'01/01/1990'	'12/12/1990'	100.00
Row 2:	'02/02/1990'	'02/02/1991'	120.00

The rows above tries to illustrate the problem, that both rows contains a (different) price for the same date range(02/02 to 12/12).

I know that I can use triggers to test for overlap, but that solution doesn't scale, and the table may contain 1M rows or more.

TIA
Bjørn Augestad Received on Wed Jun 02 1999 - 07:11:16 CDT