Re: Constraint problem: number of rows...

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Mon, 13 Jun 2005 20:34:16 +0200
Message-ID: <d8kjj9$63q$1@nntp.fujitsu-siemens.com>

HansF wrote:
> 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?

At first glance probably not, but this needs a closer look. But even if this would work, woundn't I still need to match the number of rows of the "nestee" against the attribute in the "nester"?

> If external, I suspect it's time to start looking at the triggers.
Hm. Yes, that could work. First insert the pins, then upon insertion of the symbol the trigger checks. What happens if someone inserts a pin row and does not update the pin count? Can I have a trigger fire at the end of a transaction?

> 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);
Yes, wouldn't it?

Lots of Greetings and thanks!
Volker Received on Mon Jun 13 2005 - 13:34:16 CDT