[ Team LiB ]
Recipe 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—
p
rimary
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 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON " +
"rc.UNIQUE_CONSTRAINT_NAME=kcuP.CONSTRAINT_NAME " +
"LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON " +
"rc.CONSTRAINT_NAME=kcuC.CONSTRAINT_NAME AND " +
"kcuP.ORDINAL_POSITION=kcuC.ORDINAL_POSITION " +
"ORDER BY kcuP.TABLE_NAME, kcuC.TABLE_NAME,
kcuP.ORDINAL_POSITION";
private const String GETCHECKCONSTRAINTS =
"SELECT tc.TABLE_NAME, tc.CONSTRAINT_NAME, cc.CHECK_CLAUSE " +
"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " +
"JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON " +
"tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME " +
"WHERE CONSTRAINT_TYPE='CHECK' " +
"ORDER BY tc.TABLE_NAME, cc.CONSTRAINT_NAME";
// . . .
private void getConstraintsButton_Click(object sender, System.EventArgs e)
{
// Create the DataAdapter to retrieve schema information.
SqlDataAdapter da = null;
if (primaryKeyRadioButton.Checked)
da = new SqlDataAdapter(GETPRIMARYKEYCONSTRAINTS,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
else if (foreignKeyRadioButton.Checked)
da = new SqlDataAdapter(GETFOREIGNKEYCONSTRAINTS,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
else if (checkRadioButton.Checked)
da = new SqlDataAdapter(GETCHECKCONSTRAINTS,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
// Create and fill table with schema information.
DataTable dt = new DataTable( );
da.Fill(dt);
// Bind the default view of the table with the grid.
constraintsDataGrid.DataSource = dt.DefaultView;
}
Discussion
Information schema views were first available in SQL Server 7.0 and later. They provide
system-table independent access to SQL Server metadata. The views are based on system
tables and provide a layer of abstraction that allows applications to continue to work
properly if the system tables change in future releases of SQL Server. Information
schema views provide an alternative to using system stored procedures that were
previously and are still available. The INFORMATION_SCHEMA views conform to the
SQL-92 Standard.
Information schema views are defined within each database in a schema named
INFORMATION_SCHEMA. To access the views, specify the fully qualified view name.
In the solution, for example, the view containing metadata about the tables in the
database is accessed using the following syntax:
INFORMATION_SCHEMA.TABLES
The metadata returned is limited to that which the user has permission to view. Like any
other views, information schema views can also be joined in queries or participate in
complex queries to extract specific information. For detailed information about the
different views available, refer to SQL Server Books Online.
The following three subsections explain how the solution retrieves details about the
primary key, the foreign key, and the check constraints in the database. The information
schema views that are used in the solution are described in the subsection following those
subsections.
Primary key constraints
Primary key information is obtained by querying the TABLE_CONSTRAINTS (Table 6-
11) and KEY_COLUMN_USAGE (Table 6-9) information schema views. The views are
j
oined on the CONSTRAINT_NAME field and restricted to constraints with a
CONSTRAINT_TYPE of Primary Key. The result set is sorted on the TABLE_NAME,
COLUMN_NAME, and ORDINAL_POSITION fields.
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
Foreign key constraints
Foreign key information is obtained by querying the REFERENTIAL_CONSTRAINTS
(Table 6-10) and KEY_COLUMN_USAGE (Table 6-9) information schema views. The
REFERENTIAL_CONSTRAINTS view is joined to the KEY_COLUMN_USAGE view
on the UNIQUE_CONSTRAINT_NAME column to return information about the parent
table and its columns. The REFERENTIAL_CONSTRAINTS view is joined again to the
KEY_COLUMN_USAGE view on the CONSTRAINT_NAME matching the
ORDINAL_POSITION of the parent column to return information about the child table
and its columns. The result set is sorted in ascending order on the parent
TABLE_NAME, child TABLE_NAME, and parent constraint column
ORDINAL_POSITION.
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 JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON
rc.UNIQUE_CONSTRAINT_NAME = kcuP.CONSTRAINT_NAME LEFT JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON
rc.CONSTRAINT_NAME = kcuC.CONSTRAINT_NAME AND
kcuP.ORDINAL_POSITION = kcuC.ORDINAL_POSITION
ORDER BY
kcuP.TABLE_NAME,
kcuC.TABLE_NAME,
kcuP.ORDINAL_POSITION;
Check constraint
Check constraint information is obtained by querying the TABLE_CONSTRAINTS
(Table 6-11) and CHECK_CONSTRAINTS (Table 6-8) information schema views. The
views are joined on the CONSTRAINT_NAME field and restricted to constraints with a
CONSTRAINT_TYPE of CHECK. The result set is sorted on the TABLE_NAME and
CONSTRAINT_NAME fields.
SELECT
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON
tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE
CONSTRAINT_TYPE = 'CHECK';
ORDER BY
tc.TABLE_NAME,
cc.CONSTRAINT_NAME
Information schema views used in this solution
The four information schema views used by the solution are described in this subsection.
Table 6-8 describes the CHECK_CONSTRAINTS information schema view that is based
on the sysobjects and syscomments system tables. This view contains one row for each
check constraint in the database.
Table 6-8. CHECK_CONSTRAINTS information schema view
Column name Data type Description
CONSTRAINT_CATALOG nvarchar(128) Constraint qualifier
CONSTRAINT_SCHEMA nvarchar(128) Constraint owner name
CONSTRAINT_NAME sysname Constraint name
CHECK_CLAUSE nvarchar(4000) Transact-SQL statement
Table 6-9 describes the KEY_COLUMN_USAGE information schema view that is based
on the sysobjects, syscolumns, sysreferences, spt_values, and sysindexes system tables.