Tag: constraints

Use OBJECTPROPERTY() to Find Out if an Object is a CHECK Constraint in SQL Server

Posted on by Ian

In SQL Server you can use the OBJECTPROPERTY() function to find out whether or not an object is a CHECK constraint.

This function accepts two parameters: the object ID, and the property for which you’re checking it for.

Therefore, you can pass the object ID as the first argument, and IsCheckCnst as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a CHECK constraint.

A return value of 1 means that it is a CHECK constraint, and a value of 0 means that it’s not.

Continue reading →

Return All Foreign Keys & CHECK Constraints in a SQL Server Database (T-SQL Examples)

Posted on by Ian

In SQL Server, you can use Transact-SQL to return a list of all foreign keys and CHECK constraints for the current database.

The examples on this page query two system views in order to retrieve this information: sys.foreign_keys and sys.check_constraints. You can query each one separately, or use UNION to display them all in a single result set.

Continue reading →

Modify a CHECK Constraint in SQL Server using T-SQL

Posted on by Ian

If you already have an existing CHECK constraint in SQL Server, but you need to modify it, you’ll need to drop it and recreate it. There’s no ALTER CONSTRAINT statement or anything similar.

So to “modify” an existing constraint:

  1. Drop the constraint using ALTER TABLE with DROP CONSTRAINT.
  2. Create the new constraint using ALTER TABLE with ADD CONSTRAINT.

Continue reading →

How to Restore Trust in a Foreign Key Constraint in SQL Server (T-SQL Examples)

Posted on by Ian

In SQL Server, a foreign key constraint (and a CHECK constraint) can be either trusted or not trusted.

When a constraint is trusted, this means that the constraint has been verified by the system. When it’s not trusted, the constraint has not been verified by the system.

Basically, when you have an untrusted constraint, you could also have invalid data in your database. By this I mean you could have data that violates the constraint.

This means that you’re no longer maintaining referential integrity within your relationships, which is not normally good practice when looking after a relational database in production.

In this article I’ll check my existing constraints for their “trustworthiness”, and then I’ll update them to become trustworthy once again.

Continue reading →

How to Enable a Foreign Key Constraint in SQL Server (T-SQL Examples)

Posted on by Ian

If you have a foreign key constraint in SQL Server that is currently disabled, you can use the code below to re-enable it.

When you enable a foreign key constraint, you have the option to specify whether or not to check any existing data in the table. This also applies when you enable a CHECK constraint.

Below are code examples of enabling a foreign key constraint, while specifying each of these different options.

Continue reading →

How to Find All Constraint Violations in a SQL Server Database

Posted on by Ian

You can run the DBCC CHECKCONSTRAINTS console command to return a list of all constraint violations in a SQL Server database.

This command checks the integrity of a specified constraint or all constraints on a specified table in the current database. It returns any foreign key and CHECK constraint violations that it finds.

You can use the ALL_CONSTRAINTS option to check both enabled and disabled constraints. If you omit this, then only enabled constraints are returned (unless you explicitly specify a constraint to check, in which case it will be returned regardless of whether it’s enabled or disabled).

Continue reading →