14.13 Creating ForeignKey Constraints

14.13 Creating ForeignKey Constraints

You want to link a column from one table to a column from another and control the behavior when the values are updated or deleted.

Technique

A foreign key denotes a relationship between two columns within two separate tables in a database. You use a foreign-key constraint to create special rules that specify the action to take for the value in the child table should the value in the parent table change. To create a foreign-key constraint, pass two DataColumn objects, one of which is from the parent DataTable and the other from the child DataTable , to the constructor of the ForeignKeyConstraint class. Add the resulting ForeignKeyConstraint object to the Constraints collection of the child DataTable .

A foreign key constraint can use one of four different rules that specify the action to take when a parent column value changes. The values are defined in the System.Data.Rule enumerated data type. Rule.None simply ignores the constraint. Rule.Cascade propagates the change from the parent to the child value. Rule.SetNull changes the child column's value to null , and Rule.SetDefault sets the value to the default value specified when the column was created. If a default value wasn't specified using the DefaultValue property defined in the DataColumn class, the value is set to null . These rules are applied to the UpdateRule and DeleteRule of the ForeignKeyConstraint object. The UpdateRule action runs whenever an existing value within the parent column changes. The DeleteRule runs whenever the parent value is removed. Listing 14.4 demonstrates a Windows Form application containing two DataGrid objects that display a parent table and a child table. A ComboBox control sets the current Rule used for both the UpdateRule and DeleteRule of the ForeignKeyConstraint . Changing this value allows you to see the relationship between the two columns of data as the parent value is updated or removed.

Listing 14.4 Foreign-Key Constraints

 using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; namespace _13_ForeignKeyConstraints {     public class Form1 : System.Windows.Forms.Form     {         private System.Windows.Forms.DataGrid dgParent;         private System.Windows.Forms.DataGrid dgChild;         private System.Windows.Forms.ComboBox cbConstraints;         private System.Windows.Forms.Label label1;         private System.Windows.Forms.Button btnSet;         private DataSet ds;         private ForeignKeyConstraint fkConstraint;         private System.ComponentModel.Container components = null;         public Form1()         {             InitializeComponent();             // fill constraint combo             cbConstraints.Items.Add("Cascade");             cbConstraints.Items.Add("SetNull");             cbConstraints.Items.Add("SetDefault");             cbConstraints.Items.Add("None");             cbConstraints.SelectedIndex = 3;             // create tables             ds = new DataSet();             DataTable parentTable = ds.Tables.Add( "ParentTable" );             DataTable childTable = ds.Tables.Add( "ChildTable" );             // create columns             parentTable.Columns.Add( "ID", typeof(int) );             DataColumn linkedParent = parentTable.Columns.Add(                 "LinkedID", typeof(int) );             childTable.Columns.Add( "ID", typeof(int) );             DataColumn linkedChild = childTable.Columns.Add(                 "LinkedID", typeof(int) );             // create the foreign key constraint and set to None             fkConstraint = new ForeignKeyConstraint( "LinkedIDFK",                 linkedParent, linkedChild );             fkConstraint.UpdateRule = Rule.None;             fkConstraint.DeleteRule = Rule.None;             // add constraint to child column             childTable.Constraints.Add( fkConstraint );             // data bind the 2 DataGrids             dgParent.SetDataBinding( ds, "ParentTable" );             dgChild.SetDataBinding( ds, "ChildTable" );         }         protected override void Dispose( bool disposing )         {             if( disposing )             {                 if (components != null)                 {                     components.Dispose();                 }             }             base.Dispose( disposing );         }         private void InitializeComponent()         {             // wizard generated code removed         }         [STAThread]         static void Main()         {             Application.Run(new Form1());         }         private void btnSet_Click(object sender, System.EventArgs e)         {             // change the constraint based on ComboBox value             if( cbConstraints.SelectedItem == null )                 return;             string constraintName = cbConstraints.Text;             if( constraintName == "None" )             {                 fkConstraint.DeleteRule = Rule.None;                 fkConstraint.UpdateRule = Rule.None;             }             else if( constraintName == "Cascade" )             {                 fkConstraint.DeleteRule = Rule.Cascade;                 fkConstraint.UpdateRule = Rule.Cascade;             }             else if( constraintName == "SetNull" )             {                 fkConstraint.DeleteRule = Rule.SetNull;                 fkConstraint.UpdateRule = Rule.SetNull;             }             else if (constraintName == "SetDefault" )             {                 fkConstraint.DeleteRule = Rule.SetDefault;                 fkConstraint.UpdateRule = Rule.SetDefault;             }         }     } } 

Comments

You use a foreign-key constraint to map a column from one table to another. A common use for foreign keys is to create a link between the two tables so that as one record is updated, the corresponding linked table is also updated. For instance, in the Northwind Products table, you can see a column named CategoryID . This value is linked to the Categories table, which allows you to look up additional information about the product's category. A question arises when you delete a category. Because the category no longer exists, do you also delete any products from the linked Products table as well by creating a foreign-key constraint and setting the DeleteRule to Rule.Cascade ? More than likely, you would want to instead use a DeleteRule or Rule.SetNull .