Articles → ADO.NET → Unique Constraint In Ado.Net

Unique Constraint In Ado.Net

Unique constraint means setting a rule on the column which states that all the values in the column should be unique. ADO.NET provides a class UniqueConstriant, this class will set the rule on DataColumn in ADO.NET. In this tutorial I will create an example to demonstrate the use of this class.

Syntax

UniqueConstraint <class_object> = new UniqueConstraint("<name_of_constraint>",<collection_of_data_columns>);

There are 8 other overloaded methods to initialize the class.

Example

To elaborate the concept first of all create a datatable and add three columns in it.

DataTable dt = new DataTable();
dt.Columns.Add("CustomerID", Type.GetType("System.Int32"));
dt.Columns.Add("CompanyName", Type.GetType("System.String"));
dt.Columns.Add("Location", Type.GetType("System.String"));

Here CustomerID,CompanyName and Location are three columns that we add in the datatable.

Unique constraint can either be applied on a single column or a collection of columns. Consider the table above if we apply constraint on CustomerName column then all the values in CustomerName column should be unique. If we apply constraint on CustomerID and CustomerName column then all the values in CustomerID column should be unique and all the values in CompanyName column should be unique.

To elaborate let us work on some code samples.

Scenario 1 – Add a constraint on CustomerID only

UniqueConstraint uc = new UniqueConstraint("CustConstraint", new DataColumn[] {
  dt.Columns["CustomerID"]
});
dt.Constraints.Add(uc);

Now add data in the table

DataRow dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC2";
dr["Location"] = "Delhi";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi6";
dt.Rows.Add(dr);

When you execute this code you will get runtime error

Column 'CustomerID' is constrained to be unique. Value '1' is already present.

Because the values in CustomerID are not unique.

Scenario 2 – Add a constraint on CustomerID and CompanyName

UniqueConstraint uc = new UniqueConstraint("CustConstraint", new DataColumn[] {
  dt.Columns["CustomerID"], dt.Columns["CompanyName"]
});
dt.Constraints.Add(uc);

Now add data in table

DataRow dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC1";
dr["Location"] = "Delhi";
dt.Rows.Add(dr);

This code will execute without any problem because the values in CustomerID is not unique but the values in CompanyName are unique.

Now change the above code

DataRow dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi6";
dt.Rows.Add(dr);

So in this case values in CustomerID and CompanyName columns are not unique so when you execute the program you will get runtime error.

Column 'CustomerID, CompanyName' is constrained to be unique. Value '1, ABC' is already present.

Scenario 3 – Add a constraint on CustomerID, CompanyName and Location column

UniqueConstraint uc = new UniqueConstraint("CustConstraint", new DataColumn[] {
  dt.Columns["CustomerID"], dt.Columns["CompanyName"], dt.Columns["Location"]
});
dt.Constraints.Add(uc);

Now add data in the table

DataRow dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi6";
dt.Rows.Add(dr);

This code will execute successfully. Now change the code to

DataRow dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["CustomerID"] = 1;
dr["CompanyName"] = "ABC";
dr["Location"] = "Delhi";
dt.Rows.Add(dr);

This code will give an error

Column 'CustomerID, CompanyName, Location' is constrained to be unique. Value '1, ABC, Delhi' is already present.

The reason is all three columns does not have unique values.

Posted By -	Karan Gupta

Posted On -	Friday, December 9, 2011

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**