Re: Constraint problem: number of rows...

From: HansF <News.Hans_at_telus.net>
Date: Mon, 13 Jun 2005 18:17:57 GMT
Message-Id: <pan.2005.06.13.18.20.56.564126@telus.net>

On Mon, 13 Jun 2005 19:38:37 +0200, Volker Hetzer interested us by writing:

> I've got a rather stupid constraint problem:
> There's a table with data on PCB symbols (like "7400" or "Resistor").
> Per symbol there's the number of pins on the symbol.
> Now, another table is supposed to have as many rows (per symbol)
> as there are pins.
> Is there any way I can have a constraint checking this?

AFAIK, a simple database constraint will not cover this. Heck, it doesn't even cover comparisons between tables. (From 9i SQL Ref manual: "The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.)

Do you need an external table, or could the other be a nested table or varray? If external, I suspect it's time to start looking at the triggers.

I've run into this several times and solved it both the above ways (triggers and varrays). Looking forward to seeing other responses!

It would be nice if 'Foreign Key' constraint lifted the 'match' restriction and allowed 'expression' instead. Then we could write

CONSTRAINT match_and_range

	FOREIGN KEY (mycol, myref) 
	REFERENCES reftab (mycol=fcol, myref<=fref);

-- 
Hans Forbrich

Canada-wide Oracle training and consulting mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries *** Received on Mon Jun 13 2005 - 13:17:57 CDT