How to add a check constraint on a table

Syntax:

ALTER TABLE

   ADD CONSTRAINT CHECK ()

Example:

It is always better to check for existence before adding any object to avoid any runtime errors:

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'CK_ProductReview_Rating' AND type = 'C')

ALTER TABLE Production.ProductReview

ADD CONSTRAINT CK_ProductReview_Rating CHECK (([Rating]>=(1) AND [Rating]<=(5)))

If you are adding a CHECK constraint on an existing table with data, and the conditions of the constraint are invalid, it would not be able to create the check constraint. However, the error it throws does not give a clear indication of the reason. It seems to indicate that a constraint already exists, so you need to check for the conditions:

Msg 547, Level 16, State 0, Line 2

The ALTER TABLE statement conflicted with the CHECK constraint "CK_ProductReview_Rating". The conflict occurred in database "AdventureWorks", table "Production.ProductReview", column 'Rating'.

How to drop a check constraint

Syntax:

ALTER TABLE DROP CONSTRAINT ;

Example:

-- Drop a check constraint

IF EXISTS (SELECT 1 FROM sys.objects WHERE NAME = 'CK_ProductReview_Rating' AND type = 'C')

ALTER TABLE Production.ProductReview DROP CONSTRAINT CK_ProductReview_Rating;

How to get the Table Name on which the Check Constraint is defined

You can use the following code block to get the information for a given check constraint

DECLARE @ConstraintName varchar(130)

SET @ConstraintName = 'CK_ProductReview_Rating'

SELECT  t.name as TableName,

            c.name as CheckConstraintName,

            c.definition as ConstraintDefinition

FROM  sys.objects t,

            sys.check_constraints c

WHERE c.parent_object_id = t.object_id

AND         c.name = @ConstraintName

Query Error when using ORDER BY if the column and * is in the SELECT list

Suppose, you have a query where you get one column explicitly and all other columns using * then you get an Ambiguous column name error.

The following query results in an error:

SELECT Title,* FROM HumanResources.Employee ORDER BY Title

Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'Title'.

The following does not give an error:

SELECT Title,* FROM HumanResources.Employee

So whats’s the solution?

Adding an alias for the table and using alias with the column in ORDER BY or providing a fully qualified path to the column in ORDER BY will fix this issue.

One of the following query will fix this issue.

SELECT Title,* FROM HumanResources.Employee e ORDER BY e.Title

SELECT Title,* FROM HumanResources.Employee ORDER BY HumanResources.Employee.Title

If the Title column was not in SELECT list, the issue would not arise. The following query works perfectly:

SELECT * FROM HumanResources.Employee ORDER BY Title

Also, if you didn’t have * in your select, but just specific list of columns, then also you do not need to have alias in order by

SELECT EmployeeID,Title FROM HumanResources.Employee ORDER BY Title

So, it’s only when you have a combination of explicit column name and a * in the SELECT you get this issue.

Weird issue or the way syntax is expected by SQL. However, you have to live by some quirks which creates jobs for DB programmers.