Foreign key constraint-does it impacts performance

Database
Junior Supporter

Foreign key constraint-does it impacts performance

Hi,

I have a table in which i need to include foreign key constraint. I have below queries about the same.

1. Does it hamper the performance if we have a foreign key constraint on a table than if i dont have. Like if we have a secondary key on a table , it impacts the performance in terms of maintaining a subtable and loads/DML operations takes time.

2. Does the FK key constraint also creates a subtable like secondary key.

thanks!!

3 REPLIES
Teradata Employee

Re: Foreign key constraint-does it impacts performance

It depends.

If you define a standard REFERENCES constraint then it will be validated for every child row insert/update and every parent row update/delete. This clearly imposes a performance penalty.

If you define a "batch enforcement" REFERENCES WITH CHECK OPTION constraint then it will be validated for every commit of a transaction that modifies the child or parent. That may in some cases have less of a performance impact.

The constraint itself does not create a subtable; but for a standard or batch constraint, the parent table must have a UPI or USI on the referenced column(s).

If you define a "not database enforced" REFERENCES WITH NO CHECK OPTION constraint then the database assumes the application will take care of any needed validation (which may or may not incur extra overhead). If your data actually violates this constraint, you won't get an error but the plans chosen by the optimizer may not return the correct results.

Junior Supporter

Re: Foreign key constraint-does it impacts performance

Hi Fred,

Thanks for the reply

If i define a references like :

CONSTRAINT REF1 FOREIGN KEY (K1 ) REFERENCES Table2 ( K2  )

Does it come into "standard REFERENCES constraint" that you mentioned as i have not specified any "with/with no check option" here.

The T2 should be a UPI , i believe. Do you have a link to the material /manual where i can get some more details before defining the foreign keys.

Junior Supporter

Re: Foreign key constraint-does it impacts performance

I read through the manuals, i am geting too confused. Does anyone has a link to a simple document explaining this. I just want to create a FK in one of the tables and that refers to another table's column.