Re: Tweaking Foreign Keys for larger tables

On 11/6/14, 2:58 AM, Simon Riggs wrote:
> On 5 November 2014 21:15, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> On 10/31/14 6:19 AM, Simon Riggs wrote:
>>> Various ways of tweaking Foreign Keys are suggested that are helpful
>>> for larger databases.
>>
>>> * INITIALLY NOT ENFORCED
>>> FK created, but is not enforced during DML.
>>> Will be/Must be marked NOT VALID when first created.
>>> We can run a VALIDATE on the constraint at any time; if it passes the
>>> check it is marked VALID and presumed to stay that way until the next
>>> VALIDATE run.
>>
>> Does that mean the FK would become invalid after every DML operation,
>> until you expicitly revalidate it? Is that practical?
>
> I think so.
>
> We store the validity on the relcache entry.
>
> Constraint would add a statement-level after trigger for insert,
> update, delete and trigger, which issues a relcache invalidation if
> the state was marked valid. Marked as deferrable initially deferred.

I don't think you'd need to invalidate on insert, or on an update that didn't touch a referenced key.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com