Re: Tweaking Foreign Keys for larger tables

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?

> ON DELETE IGNORE
> ON UPDATE IGNORE
> If we allow this specification then the FK is "one way" - we check the
> existence of a row in the referenced table, but there is no need for a
> trigger on the referenced table to enforce an action on delete or
> update, so no need to lock the referenced table when adding FKs.

Are you worried about locking the table at all, or about having to lock
many rows?