5

I noticed in the SQL Server documentation that the list of referenced columns is not a required parameter of a foreign key constraint:

<column_constraint> ::= 
    [ CONSTRAINT constraint_name ] 
    {     { PRIMARY KEY | UNIQUE } 
            (...)

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

      | CHECK (...)
    }

If I omit the ( ref_column ) part, it seems to reference the primary key of the referenced_table_name. That is most convenient. However, I cannot find any specification of this behavior so I am cautious to use it.

Does anybody know whether it is specified anywhere?

  • 5
    I don't know why you find this convenient. Why wouldn't you just specify which column(s) you mean? This way there is absolutely no ambiguity (consider the case of a multi-column primary key, or a parent table with multiple unique keys, etc). – Aaron Bertrand Sep 19 '14 at 15:05
  • Agree with Aaron. Be clear about your definitions. (Also, specifying the primary key is more logically resilient when things change.) – RLF Sep 19 '14 at 15:11
  • 1
    @AaronBertrand I think it only works when the parent table has a primary key so it's not ambiguous. At least that's how it works in Postgres, I can't check SQL-Server right now. – ypercubeᵀᴹ Sep 19 '14 at 15:11
  • 3
    @ypercube it may not be ambiguous to SQL Server in a case where there is a PK and one or more unique constraints, but it would be ambiguous to me. This just sounds like another case of "maximize productivity by saving four keystrokes and 18 milliseconds of developer time, at the cost of confusion for everyone else, forever." – Aaron Bertrand Sep 19 '14 at 15:13
  • 5
    But @discrete, while it may look convenient, it also hides the referenced columns in the FK definition. Explicit is almost always better than implicit. You can also not name your constraint and it gets an arbitrary obscure name from SQL-Server. Then when you'll need it, you have to look for it in the system tables. Why not declare it and choose the name yourself? – ypercubeᵀᴹ Sep 19 '14 at 15:14
7

The behaviour is not explicitly mentioned in any of the official SQL Server documentation I am familiar with, but the 1992 Draft SQL Standard (section 11.8.2.b) does say:

If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY. Let referenced columns be the column or columns identified by the unique columns in that unique constraint and let referenced column be one such column. The <referenced table and columns> shall be considered to implicitly specify a <reference column list> that is identical to that <unique column list>.

Translated, this means an implicit foreign key does reference the primary key of the referenced table. As others have mentioned in comments to the question, it is probably best to be explicit about the relationship though.

| improve this answer | |
  • The rule is actually: "an implicit foreign key references the primary key - and only the primary key - of the referenced table". A unique constraint will never be eligible for this (it's the same in Postgres btw). – a_horse_with_no_name Sep 19 '14 at 19:21
0

If the Pk has multiple columns, and you don't specify the columns in the Fk specification, then it may get them wrong. I chased a bug for a while that turned out to be that the columns were wrong. For example:

FOREIGN KEY (OneID, TwoID) REFERENCES MySchema.ForeignTable

was interpreted as:

FOREIGN KEY (OneID, TwoID) REFERENCES MySchema.ForeignTable(TwoID, OneID)

If there is only Pk, then leaving the Pk Columns off is no problem, and is not ambiguous.

| improve this answer | |
  • 3
    It doesn't "guess" the columns' order. It takes the order of the PK definition in the referenced table. – ypercubeᵀᴹ Sep 19 '14 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.