Working with Table Constraints :: SQL Server 2012

As the lead database developer on a new project, you notice that database validation occurs in the client application. As a result, database developers periodically run very costly queries to verify the integrity of the data. You have decided that your team should refactor the database to improve the integrity of the database and shorten the costly nightly validation queries. Answer the following questions about the actions you might take.
  1. How can you ensure that certain combinations of columns in a table have a unique value?

  2. How can you enforce that values in certain tables are restricted to specified ranges?

  3. How can you enforce that all columns that contain values from lookup tables are valid?

  4. How can you ensure that all tables have a primary key, even tables that right now do not have any primary key declared?
Answers
  1. You can ensure that certain columns or combinations of columns in a table are unique by applying primary key and unique constraints. You can also apply a unique index. Normally, it is preferable to use the declared primary key and unique constraints because they are easy to find and recognize within the SQL Server metadata and management tools. If the uniqueness of a row cannot be specified using a constraint or a unique index, you may be able to use a trigger.

  2. For simple restrictions of ranges in a table, you can use a check constraint. You can then specify the restriction in the expression value of the constraint.

  3. To enforce that lookup values are valid, you should normally use foreign key constraints. Foreign key constraints are declared constraints, and as such are known through metadata to SQL Server and the query optimizer. When joining a table that has a foreign key constraint to its lookup table, it is helpful to add an index on the foreign key column to assist join performance.

  4. You cannot actively enforce every table to have a primary key constraint. However, you can query sys.constraints to monitor the tables to make sure that every table does include a primary key.