to set foreign key or not to set?

[Jassim Rahma]

Hi, I want to ask about indexing. I have employee table and department table with employee_id and department_id primary keys. Also I have department_id in the employee table. Do I set the employee.department_id as a foreign key for a better performance? Thanks, Jassim

Technology News @ www.JassimRahma.com

[_AK_]

Creating a foreign key is required for refrential integrity and is normally a good practice. It is also true that a foreign key has a say inperformance. To make it better what you can do is to create index on the foreign keys.

.AK.

[Jassim Rahma]

I did indexed it but do I still need (or is it better) to declare it as foreign key?

Technology News @ www.JassimRahma.com

[_AK_]

As I mentioned creating a foreign key constraint is required for refrential integrety and normally is a good practice. It do have an impact on performance which can be reduced by creating an index. If you craete the index on foreign key it will reduce the lookups.

.AK.

[Jassim Rahma]

so declaring it as foreign key is a good practice and will have an impact on the performance but after declaring it do you still recommend to index it or declaring is enough?

Technology News @ www.JassimRahma.com

[_AK_]

Creating a foreign key is not mandatory but is advised to create. Here[^] is a link which tells about the importance of it. Now here[^] is another article which tells about refrential integrity and performance degradation which might give you a bit more idea about this.

.AK.

[Jassim Rahma]

Great Thanks

Technology News @ www.JassimRahma.com

[_AK_]

You are welcome.

.AK.