10.2 The UniqueConstraint

The UniqueConstraint prevents duplication in a single column or the combined value of a group of columns. The UniqueConstraint object adds two properties: Columns, which defines one or more DataColumn objects that, when taken together, must be unique; and IsPrimaryKey, which indicates whether the UniqueConstraint is required to guarantee the integrity of the primary key for the table.

The UniqueConstraint provides several overloaded constructors. Here's how you might create a UniqueConstraint that prevents duplicate CustomerID values:

// Create the UniqueConstraint object.
UniqueConstraint uc = new UniqueConstraint("ID", dt.Columns["CustomerID"]);

// Add the UniqueConstraint to the table's Constraints collection.
dt.Constraints.Add(uc);

You can also define a UniqueConstraint that encompasses several columns, such as this first and last name combination:

// Create an array with the two columns.
DataColumn[] cols = new DataColumn[] {dt.Columns["LastName"],  
  dt.Columns["FirstName"]};

// Create the UniqueConstraint object.
UniqueConstraint uc = new UniqueConstraint("FullName", cols);

// Add the UniqueConstraint to the table's Constraints collection.
dt.Constraints.Add(uc);

To create a primary key UniqueConstraint, use an overloaded version of the constructor that accepts a Boolean parameter, and specify true. Once created, you can't change the IsPrimaryKey property of a UniqueConstraint.

// Create a UniqueConstraint object that represents the primary key.
UniqueConstraint uc = new UniqueConstraint("ID", dt.Columns["CustomerID"],
  true);

// Add the UniqueConstraint to the table's Constraints collection.
dt.Constraints.Add(uc);

You can also pass constructor arguments to several overloads of the Add( ) method of a ConstraintCollection to create a UniqueConstraint. In this case, the false parameter indicates that this UniqueConstraint should not be designated as the primary key for the table.

// Add a new UniqueConstraint to the table's Constraints collection.
dt.Constraints.Add("ID", dt.Columns["CustomerID"], false);

However, it's quite possible that you won't use any of these approaches to generate UniqueConstraint objects because ADO.NET provides an even easier approach through the DataColumn object. As soon as you set the DataColumn.Unique property to true, a new UniqueConstraint is generated and added to the collection. Similarly, setting the Unique property of a column to false removes the UniqueConstraint. This process is transparent to your application, unless you need to modify the IsPrimaryKey property or create a UniqueConstraint that acts on multiple columns.

Having a UniqueConstraint in place doesn't mean you won't receive an error when updating data back to the data source. Remember, the DataSet almost always contains a subset of the total information in the database. If a unique column value conflicts with another value in the data source (but not the DataSet), the problem won't be detected until you attempt to commit changes.

If you add a UniqueConstraint on a DataColumn in which AllowDbNull is set to true, you will get only a single row with a null value in the column. If there are multiple rows with null values, ADO.NET considers it to be a duplication and a violation of the UniqueConstraint.

10.2.1 Constraints and FillSchema( )

If you fill a DataSet without using the FillSchema( ) method, ADO.NET doesn't create any Constraint objects automatically. However, you can still create and apply Constraint objects manually, as shown earlier.

If you use the FillSchema( ) to retrieve schema information, UniqueConstraint objects are created to match the restrictions you have defined in the data source. The FillSchema( ) method also attempts to designate a primary key. If a primary key column (or group of columns) is found in the result set, it's used to create a UniqueConstraint with IsPrimaryKey set to true. Otherwise, FillSchema( ) uses any non-nullable unique column returned by the query.

To see how this works, you can run the simple test in Example 10-1, which displays the automatically generated UniqueConstraint objects.

Example 10-1. Add unique constraints to a data source

// FillWithConstraints.cs - Create UniqueConstraints defined in data source

using System;
using System.Data;
using System.Data.SqlClient;

public class FillWithConstraints
{
    static string connectionString = "Data Source=localhost;" +
         "Initial Catalog=Northwind;Integrated Security=SSPI";
    static string SQL = "SELECT * FROM Categories";

    public static void Main() 
    {
        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand comSelect = new SqlCommand(SQL, con);
        SqlDataAdapter adapter = new SqlDataAdapter(comSelect);
        DataSet ds = new DataSet();

        // Execute the command.
        try
        {
            con.Open();

            adapter.FillSchema(ds, SchemaType.Mapped, "Categories");
            adapter.Fill(ds, "Categories");
        }
        catch (Exception err)
        {
            Console.WriteLine(err.ToString());
        }
        finally
        {
            con.Close();
        }

        foreach (UniqueConstraint uc in 
          ds.Tables["Categories"].Constraints)
        {
            Console.WriteLine("*** " + uc.ConstraintName + " ***");
 
            Console.Write("Primary Key: \t");
            Console.WriteLine(uc.IsPrimaryKey);
            Console.Write("Column: \t");
            Console.WriteLine(uc.Columns[0].ColumnName);
        }  
    }

}

If you omit the FillSchema( ) method call, you'll find that no constraint information is retrieved. With FillSchema( ), you'll find that exactly one UniqueConstraint has been added to the DataSet:

*** Constraint1 ***
Primary Key:    True
Column:         CategoryID

ForeignKeyContraint objects are never created automatically, regardless of whether you use FillSchema( ) when populating the DataSet.