Archive

Posts Tagged ‘sys.check_constraints’

Using Catalog Views – sys.key_constraints and sys.default_constraints

17.06.2011 No comments

While sys.check_constraints returns all CHECK constraints in the database, sys.key_constraints and sys.default_constraints returns primary/unique key constraints and DEFAULT constraints respectively.

some of the columns returned by sys.key_constraints are:

1. Name – Name of the constraint,

2. Schema_ID – Schema ID of the parent object,

3. Parent_object_id – object id of the table on which the constraint is enforced,

4. Type – PK or UQ, Primary Key or Unique Key,

5. Type_desc – Textual description of ‘Type’,

6. Create/Modify_date – Creation / Last Modification date time,

7. Unique_index_id –  ID of the index that is created to enforce the constraint

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE AdventureWorks2008R2

 

SELECT name, type, create_date, modify_date,

       SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id)

       AS 'Enforced On Table'

FROM   sys.key_constraints

Partial Result Set:

image

 

Same way, sys.default_constraints returns all DEFAULT constraints in the database, It returns below columns which are specific to DEFAULT constraints:

1. Parent_column_id – Column id on which the DEFAULT constraint is enforced,

2. Definition – Definition of DEFAULT constraint,

3. Type – ‘D’ = DEFAULT CONSTRAINT

COL_NAME() can be used to retrieve column name as:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT name, type, create_date, modify_date, definition,

       SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id)

       AS 'Enforced On Table',

       COL_NAME(parent_object_id, parent_column_id)

       AS 'Enforced On Column'

FROM   sys.default_constraints

Partial Result Set:

image

Hope This Helps!

Vishal

Using Catalog Views – sys.check_constraints

09.06.2011 1 comment

sys.check_constraints contains a row for each CHECK constraint. It contains below useful columns:

1. parent_column_id – column id for which CHECK constraint is defined

2. parent_object_id – id of the object for whose column the constraint is defined.

3. create/modify_date – creation/modification date

4. definition – definition of the constraint

for other columns returned by this view refer BOL.

 

for example, let’s create a new CHECK constraint as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

CREATE TABLE dbo.Salary

(

      ID          INT   NOT NULL,

      NAME        VARCHAR(50),

      SALARY      MONEY NOT NULL

)

 

ALTER TABLE dbo.Salary

ADD CONSTRAINT CHK_SALARY CHECK (SALARY BETWEEN 1000 AND 5000)

Result Set:

Command(s) completed successfully.

 

The CHECK constraint and it’s definition can be viewed in sys.check_constraints:

SELECT      Name, Parent_Object_ID, Create_Date, Modify_Date, Definition

FROM        sys.check_constraints

WHERE       Parent_Object_ID = OBJECT_ID('dbo.Salary')

Result Set:

image

If you need to identify the column name on which the CONSTRAINT is defined, you can JOIN sys.check_constraints with sys.columns as below:

SELECT      B.Name, A.Name, A.Create_Date, A.Modify_Date, A.Definition

FROM        sys.check_constraints A

INNER JOIN  sys.columns B ON B.object_id = A.parent_object_id

WHERE       parent_object_id = OBJECT_ID('dbo.Salary')

AND         B.column_id = A.parent_column_id

Result Set:

image

 

Hope This Helps!

Vishal