外键是否在 SQL Server 中自动编入索引?

2021-12-21 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:

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

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.

相关文章