Sql-server – Foreign key without referenced columns specification

foreign keyreferential-integritysql server

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?

Best Answer

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.

Related Solutions

MySQL – Foreign Key Constraint Fails

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

SQL Server – How to Cascade Primary Key Update to All Referencing Foreign Keys

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint.

If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update.

EDIT: Since you do not have the ON UPDATE CASCADE constraint, but you want to have that set up, it is a bit of work. SQL Server does not support altering the constraints to a new setting.

It is necessary to iterate through each table that has a FK constraint to the PK table. For each table with the FK:

ALTER TABLE to drop the existing FK constraint.
ALTER TABLE again to create the ON UPDATE CASCADE constraint for the FK in question.

This takes a bit of effort, but would result in your constraint being properly set for your case.

EDIT 2: The information that you need is found in sys.foreign_keys. You can select from that table to get all the information you need.

A post from John Paul Cook can be found here:

(http://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx)

This code will drop and create ALL FK constraints in a database. You should be able to work from that to make only the changes that you want in your database.

Best Answer

Related Solutions

MySQL – Foreign Key Constraint Fails

SQL Server – How to Cascade Primary Key Update to All Referencing Foreign Keys

Related Question