none
How many foreign keys can we have for a table?

Answers

  • Tintu,

    You need to look at the max. capacity specifications for SQL Server.

    For one column, you can have upto 16 foreign keys.
    For one table, the current recommended number is 253 although you will be limited (forced) by the performance issues before you reach that number. Enforcing constraints will help Query Optimizer to make better decisions for queries but it will also cause performance issues if you have too many FK constraints.

    For example, you make a update/delete/insert to a column on a table that has 25 FK's, Database Engine has to verify/validate this new value against all those child tables. This can cause severe performance issues on a busy OLTP system.




    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    • Marked as answer by Tintu Jose Wednesday, April 01, 2009 11:10 AM
    Wednesday, April 01, 2009 6:01 AM
  • Hi,

    It depends on your requirement.

    SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.


    Rajesh Jonnalagadda http://www.ggktech.com
    • Marked as answer by Tintu Jose Wednesday, April 01, 2009 11:10 AM
    Tuesday, March 31, 2009 12:13 PM

All replies

  • Hi,

    It depends on your requirement.

    SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.


    Rajesh Jonnalagadda http://www.ggktech.com
    • Marked as answer by Tintu Jose Wednesday, April 01, 2009 11:10 AM
    Tuesday, March 31, 2009 12:13 PM
  • Tintu,

    You need to look at the max. capacity specifications for SQL Server.

    For one column, you can have upto 16 foreign keys.
    For one table, the current recommended number is 253 although you will be limited (forced) by the performance issues before you reach that number. Enforcing constraints will help Query Optimizer to make better decisions for queries but it will also cause performance issues if you have too many FK constraints.

    For example, you make a update/delete/insert to a column on a table that has 25 FK's, Database Engine has to verify/validate this new value against all those child tables. This can cause severe performance issues on a busy OLTP system.




    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    • Marked as answer by Tintu Jose Wednesday, April 01, 2009 11:10 AM
    Wednesday, April 01, 2009 6:01 AM