Monday, October 11, 2010

How to recover constraints in SQL Server

Constraints: Constraints are one of the key factors in designing a table.

The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database. Maintaining integrity is of utmost importance for a database, so much so that we cannot trust users and applications to enforce these rules by themselves. Once integrity is lost, you may suffer these problems like:

you find customers are double billed,payments to the supplier are missing, and everyone loses faith in your application.

As you know there are 5 constraints in SQL Server they are:

1) Primary Key Constraints
2) Foreign Key Constraints
3) Unique Key Constraints
4) Check Constraints
5) DEFAULT Constraints

Primary Key constraints:The PRIMARY KEY constraint uniquely identifies each record in a database table.

Foreign Key Constraints: A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables.

Unique Constraints: a unique constraint uses an index to ensure a column (or set of columns) contains no duplicate values.

Check Constraints: Check constraints contain an expression the database will evaluate when you modify or insert a row.

Default Constraints: Default constraints apply a value to a column when an INSERT statement does not specify the value for the column.

Unfortunately sometimes issues may occur where the data becomes out of synch and one of these constraints has been violated. is may be due to disabled constraints or constraints that are later added with the NOCHECK option. Finding these issues can be done by running queries to check each of the constraints, but is there any easier way to determine if the data the constraints support has been violated?

Another approach to tackle this issue is to use the DBCC CHECKCONSTRAINTS command.

This command can be run as follows:

  • DBCC CHECKCONSTRAINTS (TableName) - checks an individual table

  • DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual constraint

  • DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints in the database

  • DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that violate constraints

  • DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query runs

But if this command also not resolve constraints errors than there is another solution to use of third-party SQL Recovery Software. The best option for you to use Stellat Phoenix SQL Recovery Software because it recovers all five constraints which mentioned above & also supports all latest versions of SQL Server like 7.0/2008/2005/2000 & Windows like Windows 7/2008/Vista/2003/XP/ 2000.

No comments:

Post a Comment