Menu Bar

Thursday, September 15, 2011

How to identify my constraints


"Hello. My name is Inigo Montoya. You killed my father. Prepare to die." - Inigo Montoya, The Princess Bride

I recently had to work out what constraints I had on one of my databases so I thought I may as well post something about identifying what constraints are in a database, in particular, what types of constraints you have.

Oracle provides the view sys.dba_constraints where all constraints are documented in the system catalog.

The constraint types (identified by the CONSTRAINT_TYPE column in the catalog view are:

  • C - Check constraint on a table
  • P - Primary Key
  • U - Unique Key
  • R - Referential Integrity constraint
  • V - With check option, on a view
  • O - With Read Only, on a view

Easy!

SQL Server 2000 had a view sys.sysconstraints that is still in the system as of SQL Server 2008, but has been deprecated and Microsoft could remove it at any time.

There is a column "status" that marked the kind of constraint by an integer value.

  • 1 - PRIMARY KEY constraint
  • 2 - UNIQUE KEY constraint
  • 3 - FOREIGN KEY constraint
  • 4 - CHECK constraint
  • 5 - DEFAULT constraint
  • 16 - column level constraint
  • 32 - table level constraint

The column colid contains the id of the column that the constraint applies to. The value is 0 if it's a table constraint.

In SQL Server 2005 and onwards, this table changes to 4 catalog views. Each constraint exists in sys.objects. As already mentioned in a previous post, every object in the database has an entry in sys.objects and the types are well defined there. (This is documented in Books Online).

The 4 views in question are now:

  • sys.check_constraints - this view contains one row for each object that is a check constraint with sys.objects.type = 'C'.
  • sys.default_constraints - this view contains one row for each object that is a default constraint (from create or alter table as opposed to create default) with sys.objects.type = 'D'.
  • sys.key_constraints - this view contains one row for each object that is a primary key constraint or unique constraint with sys.objects.type = 'PK' or 'UQ'.
  • sys.foreign_keys - this view contains one row for each object that is a foreign key constraint with sys.objects.type = 'F'.

References: http://msdn.microsoft.com/en-us/library/ms190365.aspx http://msdn.microsoft.com/en-us/library/ms187997.aspx http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022