Foreign key constraint limit

Foreign key constraint limit

  
Hey all,

We just hit the SQL Server foreign key limit on our Outsystems DB (see this page: http://msdn.microsoft.com/en-us/library/ms175464(v=sql.105).aspx):

"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."


Does anyone know if this limitation still stands in SQL Server 2012?
Do you *really* need more than 253 foreign key contraints in a single entity?

This limit would be pretty easy to workaround by having 1-1 relationship with another entity where you keep extra foreign keys. But the question remains: do you *really* need this?

Also, what do you mean you "hit the limit"? That statement says there is no enforced limit. What's happening?
Hi,

Also you do realise that inserting/uptating one line of that table would mean 253+ index seeks to validate all the constraints .. thats a high impact on performance.

Regards,
João Rosado
Hi all,

The entity in question is a user table (equivalent to the USER_MASTER from Enterprise Manager). We have "UserIds" in numerous entities with a factory of about 60 espaces and many more entities So yes, we do need that many foreign keys, unfortunatly.

Fortunatly, the only restriction when hitting the "foreign key limit" is that SQL Server doesn't allow you do delete rows from that entity (that's how we find out that we passed the limit).

I was just wondering if SQL Server 2012 still has the same "limit" or near.
Oh ,thats a bit diferent then...
Me and Ricardo read it as 1 table with 253+ attributes referencing other tables.

Stil are you sure it's that "limit"? Or just a constraint violation due to "Protect" rules?

Can you paste here the exact error you get?

Regards,
João Rosado

I'm sure. Here is the error:

"The query processor ran out of stack space during query optimization. Please simplify the query"