ADO.NET 3.5 Cookbook, 2nd Edition

2.9. Creating a Foreign Key Constraint

Problem

You need to create a foreign key constraint on one or more columns in a table to implement referential integrity between parent and child tables.

Solution

Use the System.Data.ForeignConstraint class.

The solution creates a DataSet containing two tables and adds a foreign key constraint. Rows are added to both parent and child tables, including a row that violates the constraint. The results are output to the console.

The C# code in Program.cs in the project CreateForeignKeyConstraint is shown in Example 2-9.

Example 2-9. File: Program.cs forCreateForeignKeyConstraint solution

using System;
using System.Data;

namespace CreateForeignKeyConstraint
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet ds = new DataSet();

            // Create the parent table and add to the DataSet
            DataTable dt1 = new DataTable("Table-1");
            dt1.Columns.Add("Id1", typeof(int));
            dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            ds.Tables.Add(dt1);

            // Create the child table and add to the DataSet
            DataTable dt2 = new DataTable("Table-2");
            dt2.Columns.Add("Id2", typeof(int));
            dt2.Columns.Add("Id1", typeof(int));
            dt2.Columns.Add("Field2", typeof(string)).MaxLength = 50;
            ds.Tables.Add(dt2);

            // Create the foreign key constraint and add to the
            // child table
            ForeignKeyConstraint fk = new ForeignKeyConstraint(
                "ForeignKey", dt1.Columns["Id1"], dt2.Columns["Id1"]);
            dt2.Constraints.Add(fk);

            try
            {
                AddParentRecord(dt1, 1, "Value 1.1");
                AddParentRecord(dt1, 2, "Value 1.2"); ...