"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
