Re: foreign key constraints with inhertiance, hack suggestions?

George Nychis wrote:
> Basically I have a master 'flows' table which is partitioned and has
> non-overlapping CHECK constraints on each partition. Each record in the
> partitions have a unique pair of attributes: interval, flow_id
>
> When inserting in to another table 'flow_labels', these two attributes
> are given, I want to ensure that there exists a single flow in some
> partition in the 'flows' table that has these two values.
>
> I'm no DB expert, but could there be some sort of rule or trigger on
> insert into 'flow_labels' that does a count() where these two values are
> exact to ensure there exists 1 flow? Problem is my 'flows' table is on
> the order of billions of flows, each partition having hundreds of
> thousands.

I think count() would be a last resort solution. If possible, I think it
would help to add the interval data to your 'flow_labels' table and join
on the combined key. That way the information that divides your 'flows'
into different tables is available at join time, and constraint
exclusion could do its work.

That'd still require some way to inherit constraints, but it's a start.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //