外键是否在 SQL Server 中自动建立索引?

2022-01-20 00:00:00 indexing foreign-keys sql-server

以下 SQL 语句会自动在 Table1.Table1Column 上创建索引，还是必须显式创建?

Would the following SQL statement automatically create an index on Table1.Table1Column, or must one be explicitly created?

数据库引擎是 SQL Server 2000

Database engine is SQL Server 2000

       CREATE TABLE [Table1] (
. . .
            CONSTRAINT [FK_Table1_Table2] FOREIGN KEY 
            (
                [Table1Column]
            ) REFERENCES [Table2] (
                [Table2ID]
            )

        )

推荐答案

SQL Server 不会自动在外键上创建索引.同样来自 MSDN:

SQL Server will not automatically create an index on a foreign key. Also from MSDN:

FOREIGN KEY 约束没有仅链接到主键另一个表中的约束；它可以也被定义为引用UNIQUE 约束的列另一张桌子.外键约束可以包含空值；但是，如果复合材料的任何一列FOREIGN KEY 约束包含 null值，验证所有值构成外键约束被跳过.确保;确定复合 FOREIGN 的所有值KEY 约束已验证，指定对所有参与的人都不是 NULL列.

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

相关文章