这两种定义外键的方式有何不同？

In the following table definition, what is the difference between these two column definitions, or does the second just create an automatically named foreign key index?

在下面的表定义中，这两个列定义之间有什么区别，或者第二个只是创建一个自动命名的外键索引？

CREATE TABLE dbo.Employee
(
    dept_id     int  NOT NULL  
        CONSTRAINT fk_employee_deptid FOREIGN KEY REFERENCES Department(dept_id),
    empType_id  int  NOT NULL  REFERENCES  EmployeeType(empType_id)
    /* ... other columns ... */
);

3 个解决方案

#1

The only difference is that the second one will be given a system generated name that will likely be more cryptic than one you allocate yourself.

唯一的区别是第二个将被赋予一个系统生成的名称，可能比你自己分配的名称更加神秘。

The column name is also optional where there is an unambiguous possibility.

如果有明确的可能性，列名也是可选的。

empType_id  int  NOT NULL  REFERENCES  EmployeeType

can also work. Again no difference in the end result. The full grammar for the FK declaration is

也可以工作。最终结果再没有区别。 FK声明的完整语法是

[ CONSTRAINT constraint_name ] 
{      [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
 }

Optional items are enclosed in square brackets.

可选项目用方括号括起来。

#2

The only real difference is that the latter one will get a system-defined name. The former is the recommended method, though I will admit I am lazy and use the latter occasionally.

唯一真正的区别是后者将获得系统定义的名称。前者是推荐的方法，虽然我会承认我很懒，偶尔使用后者。

One other difference you will notice, if you're looking closely, is that the column sys.foreign_keys.is_system_named is set to 1 if you don't specify the name yourself.

如果您仔细观察，另外一个区别是，如果您没有自己指定名称，则sys.foreign_keys.is_system_named列将设置为1。

#3

And in answer to your second question, neither creates an index. If you want the FK field to be indexed (and most of the time you do) then you need to create an index on the field.

在回答第二个问题时，既没有创建索引。如果要将FK字段编入索引（并且大多数时间都这样做），则需要在字段上创建索引。

#1