How Many Foreign Key Can You Have on A Single Table? – Interview Question of the Week #096

Question: How Many Foreign Key Can You Have on A Single Table?

I was asked above question in my recent SQLPASS presentations. I quickly answered that it is 253. However, after I returned home when I searched a bit more, I realized that my answer is not complete, hence this blog post.

Answer: SQL Server 2014 and earlier versions, supports 253 as a maximum foreign key table references per table. However, this limitation, changes in SQL Server 2016.

To understand the limitations in SQL Server 2016 we need to understand two important concepts

Solarwinds

a) Outgoing Foreign Key References – A column in a table referring other table columns.

b) Incoming Foreign Key References – A column in a table which is referenced by other table columns.

Well, above definition can be still a bit confusing, so please refer to following image for additional clarification.

How Many Foreign Key Can You Have on A Single Table? - Interview Question of the Week #096 foreignkey-800x511

Now we know the answer of two important key words, let us see the answer to our original question.

A table can reference a maximum of 253 other tables and columns as foreign keys (Outgoing Foreign Key References). SQL Server 2016 increases the limit for the number of other table and columns that can reference columns in a single table (Incoming Foreign Key References), from 253 to 10,000. However, self referencing FK (where table column is referencing itself in the same table) still is limited to 253 in all the versions of SQL Server.

Here is limitation from Books On Line-

  • Greater than 253 FK references are supported for DELETE and UPDATE DML operations. MERGE operations are not supported.
  • A table with a FK  reference to itself is still limited to 253 foreign key references.
  • Greater than 253 FK  references are not currently available for column store indexes, memory-optimized tables, or Stretch Database.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
Performance Comparison EXCEPT vs NOT IN – Interview Question of the Week #095
Next Post
How to Find Size of All the Indexes on the Database – Interview Question of the Week #097

Related Posts

11 Comments. Leave new

  • In theory, if the table has (n) non-null columns, then all possible subsets could be possible could be potential foreign keys. This is called a Catalan number and the formula is given by:

    C(n) = (2n)!/ ((n+1)! n!)

    The exponential expressions tell you that this number gets big pretty fast. The series is actually 1, 1, 2, 5, 14, 42, 132, 429, 1430, 4862, 16796, etc.

    Reply
  • And what about indexed views, do they count as indexes on the base tables?

    Reply
  • Sorry, read that as indexes rather than FKs, please delete comment.

    Reply
  • What is the use cases for a self referencing FK?

    Reply
  • Kuznetsov’s History Table is a SQL idiom which builds a temporal chain from the current row to the previous row. This is easier to show with code:

    CREATE TABLE Tasks
    (task_id INTEGER NOT NULL,
    task_score CHAR(1) NOT NULL,
    previous_end_date DATE, — null means first task
    current_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT previous_end_date_and_current_start_in_sequence
    CHECK (prev_end_date <= current_start_date),
    current_end_date DATE, — null means unfinished current task
    CONSTRAINT current_start_and_end_dates_in_sequence
    CHECK (current_start_date <= current_end_date),
    CONSTRAINT end_dates_in_sequence
    CHECK (previous_end_date current_end_date)
    PRIMARY KEY (task_id, current_start_date),
    UNIQUE (task_id, previous_end_date), — null first task
    UNIQUE (task_id, current_end_date), — one null current task
    FOREIGN KEY (task_id, previous_end_date) — self-reference
    REFERENCES Tasks (task_id, current_end_date));

    Well, that looks complicated! Let’s look at it column by column. Task_id explains itself. The previous_end_date will not have a value for the first task in the chain, so it is NULL-able. The current_start_date and current_end_date are the same data elements, temporal sequence and PRIMARY KEY constraints we had in the simple history table schema.

    The two UNIQUE constraints will allow one NULL in their pairs of columns and prevent duplicates. Remember that UNIQUE is not like PRIMARY KEY, which implies UNIQUE NOT NULL.

    Finally, the FOREIGN KEY is the real trick. Obviously, the previous task has to end when the current task started for them to abut., so there is another constraint. This constraint is a self-reference that makes sure this is true. Modifying data in this type of table is easy, but requires some thought.. Fortunately, Alex has written a Simple Talk article to explain in more detail how it is done.

    Disabling Constraints

    Just one little problem with that FOREIGN KEY constraint. It will not let you put the first task into the table. There is nothing for the constraint to reference. In Standard SQL, we can declare constraints to be DEFERABLE with some other options. The idea is that you can turn a constraint ON or OFF during a session so the database can be in state that would otherwise be illegal. But at the end of the session all constraints have to be TRUE. or UNKNOWN.

    In SQL Server, you can disable constraints and then turn them back on. It actually is restricted to disabling FOREIGN KEY constraint, and CHECK constraints. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always enforced. The syntax for this is part of the ALTER TABLE statement. The syntax is simple:

    ALTER TABLE NOCHECK CONSTRAINT [ | ALL];
    This is why you want to name the constraints; without user given names, you have to look up what the system gave you and they are always long and messy.. The ALL option will disable all of the constraints in the entire schema. Be careful with it.

    To re-enable, the syntax is similar and explains itself:

    ALTER TABLE CHECK CONSTRAINT [ | ALL];
    When a disabled constraint is re-enabled, the database does not check to ensure any of the existing data meets the constraints. So for this table, The body of a procedure to get things started would look like this:

    BEGIN
    ALTER TABLE Tasks NOCHECK CONSTRAINT ALL;
    INSERT INTO Tasks (task_id, task_score, current_start_date, current_end_date, previous_end_date)
    VALUES (1, ‘A’, ‘2010-11-01’, ‘2010-11-03’, NULL);
    ALTER TABLE Tasks CHECK CONSTRAINT ALL;
    END;

    Reply
  • Chamika Goonetilaka
    November 9, 2016 9:42 am

    Parent-Child relationships.

    Reply
  • Rafal Ziolkowski
    February 1, 2017 12:48 pm

    I think I am bit confused by Outgoing(this is clear) vs Incoming(not clear) FKs. Could you give some real live examples? Thanks!

    Reply
    • Parent – Child relationship.

      Reply
      • Rafal Ziolkowski
        February 2, 2017 3:19 pm

        Ok, I understand it is Parent-Child in both cases, the difference is just about the direction of FK relationship. By the way I was surprised to see that there is a limit on how many FKs can reference given table. Thanks for bringing this up!

  • Thanks Rafal. You are right, its just a direction and both are FK.

    Reply

Leave a Reply

Menu