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 informationprimary key, foreign key, or check constraintfrom 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 joined 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. This view contains one row for each column in the current database that is constrained as a key.
Table 6-9. KEY_COLUMN_USAGE information schema view
|
Column name |
Data type |
Description |
|---|---|---|
|
CONSTRAINT_CATALOG |
nvarchar(128) |
Constraint qualifier |
|
CONSTRAINT_SCHEMA |
nvarchar(128) |
Constraint owner name |
|
CONSTRAINT_NAME |
nvarchar(128) |
Constraint name |
|
TABLE_CATALOG |
nvarchar(128) |
Table qualifier |
|
TABLE_SCHEMA |
nvarchar(128) |
Table owner name |
|
TABLE_NAME |
nvarchar(128) |
Table name |
|
COLUMN_NAME |
nvarchar(128) |
Column name |
|
ORDINAL_POSITION |
int |
Column ordinal |
Table 6-10 describes the REFERENTIAL_CONSTRAINTS information schema view that is based on the sysobjects , sysreferences , and sysindexes system tables. This view contains one row for each foreign key constraint in the current database.
Table 6-10. REFERENTIAL_CONSTRAINTS information schema view
|
Column name |
Data type |
Description |
|---|---|---|
|
CONSTRAINT_CATALOG |
nvarchar(128) |
Database name |
|
CONSTRAINT_SCHEMA |
nvarchar(128) |
Constraint owner name |
|
CONSTRAINT_NAME |
sysname |
Constraint name |
|
UNIQUE_CONSTRAINT_CATALOG |
nvarchar(128) |
Unique database name |
|
UNIQUE_CONSTRAINT_SCHEMA |
nvarchar(128) |
Unique constraint owner name |
|
UNIQUE_CONSTRAINT_NAME |
sysname |
Unique constraint name |
|
MATCH_OPTION |
varchar(7) |
Referential constraint-matching condition |
|
UPDATE_RULE |
varchar(9) |
Action taken if the T-SQL UPDATE statement violates referential integrity defined by the constraint |
|
DELETE_RULE |
varchar(9) |
Action taken if the T-SQL DELETE statement violates referential integrity defined by the constraint |
Table 6-11 describes the TABLE_CONSTRAINTS information schema view that is based on the sysobjects system table. This view contains one row for each table constraint in the current database.
Table 6-11. TABLE_CONSTRAINTS information schema view
|
Column name |
Data type |
Description |
|---|---|---|
|
CONSTRAINT_CATALOG |
nvarchar(128) |
Constraint name |
|
CONSTRAINT_SCHEMA |
nvarchar(128) |
Constraint owner |
|
CONSTRAINT_NAME |
sysname |
Constraint name |
|
TABLE_CATALOG |
nvarchar(128) |
Database name |
|
TABLE_SCHEMA |
nvarchar(128) |
Table owner |
|
TABLE_NAME |
sysname |
Table name |
|
CONSTRAINT_TYPE |
varchar(11) |
Type of constraint: CHECK , UNIQUE , PRIMARY KEY , or FOREIGN KEY |
|
IS_DEFERRABLE |
varchar(2) |
Specifies whether constraint checking can be deferred; always returns the value NO . |
|
INITIALLY_DEFERRED |
varchar(2) |
Specifies whether constraint checking is initially deferred; always returns the value NO |