Are these two ways of defining foreign keys different?

Question

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 ... */
);

Note that while a foreign key constraint can help the optimizer, it isn't technically an index. — Aaron Bertrand, Sep 06 '11 at 21:20
There is **no index** being generated automatically for foreign keys in SQL Server - none, never. See: [Does a foreign key automatically create an index?](http://stackoverflow.com/questions/836167/does-a-foreign-key-automatically-create-an-index/836176#836176) — marc_s, Sep 07 '11 at 05:03

Martin Smith · Accepted Answer · 2011-09-06T21:26:05.107

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

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

Damn, I'm not sure whether to suspect me of reading over your shoulder, or you of reading over mine. :-) — Aaron Bertrand, Sep 06 '11 at 21:18

Aaron Bertrand · Answer 2 · 2011-09-06T21:28:04.120

3

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.

edited Sep 06 '11 at 21:28

answered Sep 06 '11 at 21:18

Aaron Bertrand

272,866
37
466
490

score 2 · Answer 3 · answered Sep 06 '11 at 21:55

2

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.

answered Sep 06 '11 at 21:55

HLGEM

94,695
15
113
186

Thank you for the additional answer. – Squirrelsama Sep 07 '11 at 21:43

Are these two ways of defining foreign keys different?

3 Answers3