All Forums Database
teradatauser2 236 posts Joined 04/12
10 Aug 2012
Foreign key constraint-does it impacts performance


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.


Fred 1096 posts Joined 08/04
11 Aug 2012

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.

teradatauser2 236 posts Joined 04/12
12 Aug 2012

Hi Fred,

Thanks for the reply

If i define a references like :


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.

teradatauser2 236 posts Joined 04/12
13 Aug 2012

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.

You must sign in to leave a comment.