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.
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.
// 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