This article demonstrates how to add a CHECK constraint to an existing table.
You can add a constraint to an existing table by using the ALTER TABLE statement along with the ADD CONSTRAINT argument. Examples below.
Author: Ian
Rename a CHECK Constraint in SQL Server using T-SQL
You can use the sp_rename system stored procedure to rename a CHECK constraint in SQL Server.
The purpose of this stored procedure is to allow you to rename user-created objects in the current database. So you can also use it to rename other objects such as tables, columns, alias data types, etc.
Rename a Foreign Key in SQL Server using T-SQL
You can use the sp_rename system stored procedure to rename a foreign key constraint in SQL Server.
The purpose of this stored procedure is to allow you to rename user-created objects in the current database, so you can also rename other objects such as tables, columns, alias data types, etc.
How to Restore Trust in a Foreign Key Constraint in SQL Server (T-SQL Examples)
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.
How to Enable a Foreign Key Constraint in SQL Server (T-SQL Examples)
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.
How to Find All Constraint Violations in a SQL Server Database
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).
How to Return All Untrusted Foreign Key Constraints in SQL Server (T-SQL Example)
To return a list of all untrusted foreign key constraints in a SQL Server database, you can run the T-SQL code below.
An untrusted foreign key is one that has its is_not_trusted flag set to 1.
How to Return All Disabled Foreign Key Constraints in SQL Server (T-SQL Example)
If you need to return a list of all foreign key constraints that have been disabled in a SQL Server database, you can run the T-SQL code below.
How to Disable a Foreign Key Constraint in SQL Server (T-SQL Examples)
If you find yourself in the situation where you need to disable a foreign key constraint in SQL Server, here’s how to do that using Transact-SQL.
This will allow you to enter data without being restricted by the foreign key. Obviously, you wouldn’t do this unless you had a very good reason to do so. Foreign keys enforce referential integrity, so disabling them has the potential to create all sorts of issues.
How to Return All Untrusted CHECK Constraints in SQL Server (T-SQL Example)
If you need to return a list of all untrusted CHECK constraints in a SQL Server database, you can run the T-SQL code below.
By “untrusted”, I’m referring to those constraints that have their is_not_trusted flag set to 1.