ADO.NET Cookbook

6.9. Retrieving Constraints from a SQL Server Database

Problem

You need to programmatically define constraints in a DataSet and retrieve constraint information defined in a SQL Server database.

Solution

Use the INFORMATION_SCHEMA views and SQL Server system tables to get information about primary keys, foreign keys, and check constraints.

The sample code contains one event handler:

Get Constraints Button.Click

Uses a SQL select statement to load the specified constraint information—primary key, foreign key, or check constraint—from the INFORMATION_SCHEMA views into a DataTable.

The C# code is shown in Example 6-27.

Example 6-27. File: ConstraintForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; private const String GETPRIMARYKEYCONSTRAINTS = "SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, " + "kcu.COLUMN_NAME, kcu.ORDINAL_POSITION " + "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " + "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON " + "tc.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME " + "WHERE tc.CONSTRAINT_TYPE='PRIMARY KEY' " + "ORDER BY tc.TABLE_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION"; private const String GETFOREIGNKEYCONSTRAINTS = "SELECT rc.CONSTRAINT_NAME, rc.UPDATE_RULE, rc.DELETE_RULE, " + "kcuP.TABLE_NAME ParentTable, kcuC.TABLE_NAME ChildTable, " + "kcuP.COLUMN_NAME ParentColumn, kcuC.COLUMN_NAME ChildColumn " + "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc " + "LEFT ...