sql-server - SQL Server : Maximum number of constraints referencing a table

Question

在我们的数据库中，大约有 1000 个表引用表:FAMILIES。当我们尝试从 FAMILIES 中删除记录时，我们看到以下错误:

消息 8621，第 17 级，状态 1，第 81 行查询处理器在查询优化期间耗尽了堆栈空间。请简化查询。

我注意到，如果表有超过 918 个引用该表的外部约束，我们可以重现此问题。下面是重现该错误的代码。

下面的代码: 创建表 FAMILIES 插入样本记录 创建引用 FAMILIES 的表(@tbl_count 是要创建的表的数量) 从 FAMILIES 中删除记录

如果我们将 @tbl_count 的值更改为 918 或更小，则代码可以正常工作。

1. 表可以拥有的约束(或外键引用)的最大数量是多少？
2. 如果有超过 1000 个表引用某个表，是否有办法解决该错误？

此问题在 SQL Server 2012 和 2014 中可以重现，但在 2016 中运行良好。我正在 Windows10 上工作。

BEGIN TRANSACTION
  CREATE TABLE families (fmly_key BIGINT, PRIMARY KEY (fmly_key))
  INSERT INTO families VALUES (10)

  DECLARE @v_count BIGINT = 0, @sql_stmt NVARCHAR(MAX), @tbl_count BIGINT = 919

  WHILE @v_count < @tbl_count
  BEGIN
    SET @v_count = @v_count + 1
    SET @sql_stmt = 'CREATE TABLE TABLE'+cast(@v_count as nvarchar(max))+' (COL1 BIGINT, FOREIGN KEY (COL1) REFERENCES FAMILIES (FMLY_KEY))'
    exec sp_executesql @sql_stmt  
  END

  DELETE FROM families WHERE fmly_key = 10
ROLLBACK TRANSACTION

Answer 1

SQL 2016 中提高了支持的限制。请参阅

the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the 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.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2014

SQL Server 2016 (13.x) increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2016