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.Each one plays an important role in your database architecture.

The following are the constraints in SQL server

1. Primary Key Constraint - specify fields that uniquely identify each record in the table. (NULL is not allowed.)
2. Foreign Key Constraint - are fields in a relational database table that match the primary key column of another table to enforce data integrity. Both datatype should be the same. The optimizer uses foreign key constrains and check constraints to create more efficient query plan by skipping some part of the query plan because the optimizer can see that there is a foreign key constraint so it is not necessary to execute that part of the plan.Consider creating an index for every foreign key constraint.
3. Default Constraint - allow you to specify a value that the database will use to populate fields that are left blank in the input source.
4. Unique Constraint - specify fields that uniquely identify each record in the table.NULL is allowed for one time.

5. Check Constraint - allow you to limit the types of data that users may insert in a database. If you have a choice between using a trigger or a CHECK constraint to enforce rules or defaults within your SQL Server databases, you will generally want to choose a CHECK constraint, as they are faster than using triggers when performing the same task.Rules are provided for backward compatibility and have been replaced by CHECK constraints. Constraints are much more efficient than rules and can boost performance. Rules have some restrictions. Avoid using CHECK_CONSTRAINTS hint with bulk copy program.

6. NOT NULL Constraint - NOT NULL constraints in Microsoft SQL Server allow you to specify that a column may not contain NULL values.(It behaves like a constraint.)

It is always a good practice to create the constraint with a proper name. Otherwise as discussed in the question there would be a possiblity for junk constraint names there by many confusion. Following are the two ways to create constraints in SQL server with proper naming conventions.

The Query Optimizer can use constraints to help it select the best performing query. In order to get the best performance, it is important that you use them appropriately throughout your database application, such as using them for primary keys, unique keys, primary keys, and so on

Tips for using SQL Server 2008 constraints

1. Use CHECK constraints instead of triggers whenever possible.

2. Use CHECK constraints instead of rules.

3. Consider creating unique constraints and primary keys on user-defined table types.

4. Avoid using CHECK_CONSTRAINTS hint with bulk copy program.

5. Consider creating column-level constraints instead of the table-level constraints.

6. Consider creating an index for every foreign key constraint.

7. Try to create a single column constraint.

8. Consider creating a surrogate integer primary key.

9. Use cascading referential integrity constraints instead of triggers whenever possible.

10. Use default constraints instead of DEFAULT objects.

11. When you create primary key or unique key constraints, specify the CLUSTERED or NONCLUSTERED keyword.

12. Create NONCLUSTERED primary key for identity column.

13. When you create primary key or unique key constraints, specify a FILLFACTOR keyword.

14. Consider using the NOT FOR REPLICATION clause of the FOREIGN KEY and CHECK constraints.

**--Method 1:At Declaration level**

CREATE TABLE SQLTypes(
    [Type] CHAR(10) NOT NULL
        CONSTRAINT UNQ__SQLTypes__Type UNIQUE
)

CREATE TABLE SQLZealot (
      ID int NOT NULL
        CONSTRAINT PK__SQLZealot_ID PRIMARY KEY
    , SalesPrice money NOT NULL
        CONSTRAINT CH__SQLZealot__SalesPrice
            CHECK (SalesPrice >= 1 AND SalesPrice <=100)
    , [Type] CHAR(10) NOT NULL
        CONSTRAINT FK__SQLZealot__Type FOREIGN KEY REFERENCES SQLTypes([Type])
    , DateMade datetime NOT NULL
        CONSTRAINT DF__SQLZealot_DateMade DEFAULT GetUtcDate()
);

Drop table SQLTypes
Drop Table SQLZealot


**--MEthod 2:At Later level**

 CREATE TABLE SQLTypes(
    [Type] CHAR(10) NOT NULL
)

CREATE TABLE SQLZealot (
      ID int NOT NULL
    , SalesPrice money NOT NULL
    , [Type] CHAR(10) NOT NULL
    , DateMade datetime NOT NULL
);

 Alter table SQLTypes Add CONSTRAINT UNQ__SQLTypes__Type UNIQUE(Type)

 Alter table SQLZealot Add CONSTRAINT PK__SQLZealot_ID PRIMARY KEY(ID)

 Alter table SQLZealot Add CONSTRAINT CH__SQLZealot__SalesPrice
            CHECK (SalesPrice >= 1 AND SalesPrice <=100)

 Alter table SQLZealot Add CONSTRAINT FK__SQLZealot__Type FOREIGN KEY(Type) REFERENCES SQLTypes([Type])

 Alter table SQLZealot Add CONSTRAINT DF__SQLZealot_DateMade DEFAULT GetUtcDate() for Datemade

Drop table SQLTypes
Drop Table SQLZealot

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.

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

Data integrity rules fall into one of three categories: entity, referential, and domain. We want to briefly describe these terms to provide a complete discussion.

Entity Integrity

Entity integrity ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint. For example, the ProductID column of the Products table is a primary key for the table.

Referential Integrity

Referential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint. The ProductID column of the Order Details table has a foreign key constraint applied referencing the Orders table. The constraint prevents an Order Detail record from using a ProductID that does not exist in the database. Also, you cannot remove a row from the Products table if an order detail references the ProductID of the row.

Entity and referential integrity together form key integrity.

Domain Integrity

Domain integrity ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints. These are the constraints we will cover in this article, but be aware there are other options available to enforce domain integrity. Even the selection of the data type for a column enforces domain integrity to some extent. For instance, the selection of datetime for a column data type is more restrictive than a free format varchar field.

The following list gives a sampling of domain integrity constraints.

• A product name cannot be NULL.
• A product name must be unique.
• The date of an order must not be in the future.
• The product quantity in an order must be greater than zero.

We will focus on the following constraints:

•NOT NULL

•UNIQUE

•PRIMARY KEY

•FOREIGN KEY

•CHECK

•DEFAULT

---

Different ways you can create a CONSTRAINT inside SQL Server

---

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE Persons
    (
    	P_Id int NOT NULL,
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255)
    )
    GO

---

---

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note:: that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

CREATE TABLE Persons
    (
    	P_Id int NOT NULL UNIQUE,
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255)
    )
    GO

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons
(
	P_Id int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)	
GO

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD UNIQUE (P_Id)
GO

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
    ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
    GO

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
    DROP CONSTRAINT uc_PersonID
    GO

There is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.

---

---

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:

CREATE TABLE Persons
    (
    	P_Id int NOT NULL PRIMARY KEY,
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255)
    )
    GO

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons
    (
    	P_Id int NOT NULL,
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255),
    	CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
    )
    GO

SQL PRIMARY KEY Constraint on ALTER TABLE

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
    ADD PRIMARY KEY (P_Id)
    GO

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
    ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
    GO

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

To DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons
    DROP CONSTRAINT pk_PersonID
    GO

---

---

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:

CREATE TABLE Orders
    (
    	O_Id int NOT NULL PRIMARY KEY,
    	OrderNo int NOT NULL,
    	P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
    )
    GO

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Orders
    (
    	O_Id int NOT NULL,
    	OrderNo int NOT NULL,
    	P_Id int,
    	PRIMARY KEY (O_Id),
    	CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
    	REFERENCES Persons(P_Id)
    )
    GO

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:

ALTER TABLE Orders
    ADD FOREIGN KEY (P_Id)
    REFERENCES Persons(P_Id)
    GO

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Orders
    ADD CONSTRAINT fk_PerOrders
    FOREIGN KEY (P_Id)
    REFERENCES Persons(P_Id)
    GO

To DROP a FOREIGN KEY Constraint

ALTER TABLE Orders
    DROP CONSTRAINT fk_PerOrders
    GO

---

---

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "PId" column when the "Persons" table is created. The CHECK constraint specifies that the column "PId" must only include integers greater than 0.

CREATE TABLE Persons
    (
    	P_Id int NOT NULL CHECK (P_Id>0),
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255)
    )
    GO

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons
    (
    	P_Id int NOT NULL,
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255),
    	CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
    )
    GO

SQL Check Constraints and Existing Values

As with UNIQUE constraints, adding a CHECK constraint after a table is populated runs a chance of failure, because the database will check existing data for conformance. This is not optional behavior with a unique constraint, but it is possible to avoid the conformance test when adding a CHECK constraint using WITH NOCHECK syntax in SQL.

CREATE TABLE Employees_2
    (
        EmployeeID int,
        Salary money
    )
    GO

    INSERT INTO Employees_2 VALUES(1, -1)
    GO

    ALTER TABLE Employees_2 WITH NOCHECK 
        ADD CONSTRAINT CK_Salary CHECK(Salary > 0)	
    GO

    INSERT INTO Employees_2 (EmployeeID, Salary) VALUES(2, NULL)
    GO

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
    ADD CHECK (P_Id>0)
    GO

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
    ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
    GO

To DROP a CHECK Constraint

ALTER TABLE Persons
    DROP CONSTRAINT chk_Person
    GO

---

---

SQL DEFAULT Constraint

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

CREATE TABLE Persons
    (
    	P_Id int NOT NULL,
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255) DEFAULT 'Sandnes'
    )
    GO

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

CREATE TABLE Orders
    (
    	O_Id int NOT NULL,
    	OrderNo int NOT NULL,
    	P_Id int,
    	OrderDate date DEFAULT GETDATE()
    )
    GO

SQL DEFAULT Constraint on ALTER TABLE

ALTER TABLE Persons
    ALTER COLUMN City SET DEFAULT 'SANDNES'
    GO

To DROP a DEFAULT Constraint

ALTER TABLE Persons
    ALTER COLUMN City DROP DEFAULT
    GO

Constraints:: http://odetocode.com/Articles/79.aspx

Tips for using SQL Server 2008 constraints :: http://www.sswug.org/articles/viewarticle.aspx?id=53378

Following are the CONSTRAINTS that are supported in SQL Server;

• Primary Key Constraint
• Foreign Key Constraint
• Default Constraint
• Unique Constraint
• Check Constraint

Primary Key Constraint

Primary Key Constraint: Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.

1) Create Table Statement to create Primary Key

 a.Column Level

USE AdventureWorks2008
GO

CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
); 

GO

b.Table Level

CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(25)
CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
);
GO

2) Alter Table Statement to create Primary Key

ALTER TABLE Products
ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
GO

3) Alter Statement to Drop Primary key

ALTER TABLE Products
DROP CONSTRAINT pk_products_pid;
GO

Foreign Key Constraint

When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.

1) Create Table Statement to create Foreign Key

a. Column Level

    USE AdventureWorks2008
    GO
    CREATE TABLE ProductSales
    (
    SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
    ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
    SalesPerson VARCHAR(25)
    );

GO

b. Table Level

CREATE TABLE ProductSales
(
SalesID INT,
ProductID INT,
SalesPerson VARCHAR(25)
CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
);
GO

1) Alter Table Statement to create Foreign Key

ALTER TABLE ProductSales
ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
GO

2) Alter Table Statement to Drop Foreign Key

ALTER TABLE ProductSales
DROP CONSTRAINT fk_productSales_pid;
GO

Default Constraint

Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.

1) Create Table Statement to create Default Constraint

a. Column Level

USE AdventureWorks2008
GO
CREATE TABLE Customer
(
CustomerID INT CONSTRAINT pk_customer_cid PRIMARY KEY,
CustomerName VARCHAR(30),
CustomerAddress VARCHAR(50) CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN'
);
GO

b. Table Level : Not applicable for Default Constraint

2) Alter Table Statement to Add Default Constraint

ALTER TABLE Customer
ADD CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' FOR CustomerAddress
GO

3) Alter Table to Drop Default Constraint

ALTER TABLE Customer
DROP CONSTRAINT df_customer_Add
GO

UNIQUE Constraint

You can create a UNIQUE constraint as part of the table definition when you create a table. If a table already exists, you can add a UNIQUE constraint, provided that the column or combination of columns that make up the UNIQUE constraint contains only unique values. A table can contain multiple UNIQUE constraints.

If a UNIQUE constraint already exists, you can modify or delete it. For example, you may want the UNIQUE constraint of the table to reference other columns, or you may want to change the type of index clustering.

ALTER TABLE MyTableName 
ADD CONSTRAINT     UniqueContstraintName         
UNIQUE NONCLUSTERED     (                 MyTableColumn    )

I order to drop constraint;

ALTER TABLE MyTableName DROP CONSTRAINT UniqueContstraintName;

DEFAULT Constraint

It is used to specify the default value for the column in case of NULL.

ALTER TABLE MyTableName
ADD NewCol VARCHAR(50)
CONSTRAINT DF_MyTableName_NewCol DEFAULT '' NOT NULL
GO

CHECK Constraint

CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.

CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn = 10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.

CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) FROM CheckTbl
   RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO

The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.

CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. For example, consider the following statements executed on table CheckTbl.

INSERT INTO CheckTbl VALUES (10, 10)
GO
DELETE CheckTbl WHERE col1 = 10;

The DELETE statement succeeds, even though the CHECK constraint specifies that table CheckTbl must have at least 1 row.

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.Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.For instance, we have foreign key constraints to ensure all orders reference existing products. You cannot enter an order for a product the database does not know about.

The constraint is very much needed for partition elimination and if we don’t get partition elimination , there is no use of going for Partition view or partition tables.

Constraint is not only for data integrity but also for Performance. Constraints does help optimizer

Data integrity rules fall into one of four categories:

1. Entity
2. Referential
3. Domain
4. User-Defined Integrity

Classes of Constraints

Six types of constraints supported by Microsoft SQL Server.

1. NOT NULL
2. CHECK
3. UNIQUE
4. PRIMARY KEY
5. FOREIGN KEY
6. DEFAULT

Explanation

1. NOT NULL
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

2. CHECK
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

3. UNIQUE
A UNIQUE constraint uniquely identify each record in the table.NULL is allowed for one time.The unique key constraints are used to enforce entity integrity as the primary key constraints.

4. PRIMARY KEY
A PRIMARY KEY constraint uniquely identify each record in the table. (NULL is not allowed.).Only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

5. FOREIGN KEY
A FOREIGN KEY constraint is used to enforce referential integrity. You can control the actions SQL Server takes when you attempt to update or delete a key to which existing foreign keys point. You can control it by using the ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.

6. DEFAULT
A DEFAULT constraint is used to populate fields that are left blank in the input source. it's a replacement for the use of NULL values that provide a great way to predefine common data elements.

Example using Create Table

CREATE TABLE ProductType(
    [PTypeId] INT NOT NULL
    	CONSTRAINT PK__ProductType_PTypeId PRIMARY KEY,
    [PTypeName]  VARCHAR(10) NOT NULL
        CONSTRAINT UNQ__ProductType__PTypeName UNIQUE
)

CREATE TABLE Product (
      [ProdID] INT NOT NULL
        CONSTRAINT PK__Product_ProdID PRIMARY KEY
      ,[ProdName]  VARCHAR(30) NOT NULL
        CONSTRAINT UNQ__Product__ProdName UNIQUE
    , [SalesPrice] decimal(12,2) NOT NULL
        CONSTRAINT CH__Product__SalesPrice
            CHECK (SalesPrice >= 1.0 AND SalesPrice <=5000.0)
    , [Quantity] INT NOT NULL
        CONSTRAINT CH__Product__Quantity
            CHECK (Quantity >= 1)
    , [PTypeId] INT NOT NULL
        CONSTRAINT FK__Product__ProductType FOREIGN KEY REFERENCES ProductType([PTypeId])
    , [DateEnter] datetime NOT NULL
        CONSTRAINT DF__Product_DateEnter DEFAULT GetDate()
    , [Posted] bit NOT NULL
    	CONSTRAINT DF__Product_Posted DEFAULT 0
);

--DROP Table
DROP TABLE Product
DROP TABLE ProductType

Example using Alter Table

CREATE TABLE ProductType(
    [PTypeId] INT NOT NULL,
    [PTypeName]  VARCHAR(10) NOT NULL
    )

CREATE TABLE Product (
      [ProdID] INT NOT NULL
     ,[ProdName]  VARCHAR(30) NOT NULL
     ,[SalesPrice] DECIMAL(12,2) NOT NULL
     ,[Quantity] INT NOT NULL
     ,[PTypeId] INT NOT NULL
    , [DateEnter] DATETIME NOT NULL
    , [Posted] BIT NOT NULL
);

 ALTER TABLE ProductType ADD CONSTRAINT PK__ProductType__PTypeId PRIMARY KEY(PTypeId)

 ALTER TABLE ProductType ADD CONSTRAINT UNQ__ProductType__PTypeName UNIQUE(PTypeName)

 ALTER TABLE Product ADD CONSTRAINT PK__Product_ProdID PRIMARY KEY(ProdID)

 ALTER TABLE Product ADD CONSTRAINT UNQ__Product__ProdName UNIQUE(ProdName)

 ALTER TABLE Product ADD CONSTRAINT CH__Product__SalesPrice
            CHECK (SalesPrice >= 1.0 AND SalesPrice <=5000.0)

 ALTER TABLE Product ADD CONSTRAINT CH__Product__Quantity
            CHECK (Quantity >= 1)

 ALTER TABLE Product ADD CONSTRAINT FK__Product__ProductType FOREIGN KEY(PTypeId) REFERENCES ProductType([PTypeId])

 ALTER TABLE Product ADD CONSTRAINT DF__Product_DateEnter DEFAULT GetDate() for DateEnter

 ALTER TABLE Product ADD CONSTRAINT DF__Product_Posted DEFAULT 0 for Posted

 --DROP Table
 DROP TABLE ProductType
 DROP TABLE Product

Script to find constraint for table Product and ProductType

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND OBJECT_NAME(parent_object_id)='Product'
OR OBJECT_NAME(parent_object_id)='ProductType'
ORDER BY OBJECT_NAME(parent_object_id)

you can use a UDF as a CHECK constraint for a column. Here's a simple example:

--Create Function
CREATE FUNCTION dbo.SimpleUDFCheckConstraint(@c1 AS INT) RETURNS BIT AS
BEGIN
RETURN    CASE  WHEN @c1 IN (SELECT Number FROM master..spt_values WHERE Type = 'P') 
THEN 1  ELSE 0  END
END
GO

--Create Table
CREATE TABLE t
(
c1 INT NOT NULL
CHECK (dbo.SimpleUDFCheckConstraint(c1) = CAST(1 AS BIT))
)

INSERT INTO t(c1) VALUES('1')
INSERT INTO t(c1) VALUES('-1')
INSERT INTO t(c1) VALUES('0')

SELECT *  FROM t

--Drop Table and Function

DROP TABLE t
DROP FUNCTION dbo.SimpleUDFCheckConstraint

The INSERT with -1 will fail.

Disable CHECK Constraint – Enable CHECK Constraint
Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required. This type of operation is needed when bulk loading operations are required or when there is no need to validate the constraint. The T-SQL Script that does the same is very simple.

USE AdventureWorks
GO
-- Disable the constraint
ALTER TABLE HumanResources.Employee
NOCHECK CONSTRAINT CK_Employee_BirthDate
GO
-- Enable the constraint
ALTER TABLE HumanResources.Employee
WITH CHECK CHECK CONSTRAINT CK_Employee_BirthDate
GO

http://blog.sqlauthority.com/2007/09/16/sql-server-2005-list-all-the-constraint-of-database-find-primary-key-and-foreign-key-constraint-in-database

http://blog.sqlauthority.com/2009/11/12/sql-server-disable-check-constraint-enable-check-constraint

Constraints let you define the way the Database Engine automatically enforces the integrity of a database.

NOT NULL specifies that the column does not accept NULL values.

CHECK constraints enforce domain integrity by limiting the values that can be put in a column. CREATE TABLE custsample ( custid int PRIMARY KEY, custname char(50), custaddress char(50), custcreditlimit money, CONSTRAINT chkid CHECK (custid BETWEEN 0 and 10000 ) )

UNIQUE constraints enforce the uniqueness of the values in a set of columns.

PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. CREATE TABLE partsample (partnmbr int PRIMARY KEY, partname char(30), partweight decimal(6,2), part_color char(15) );

FOREIGN KEY constraints identify and enforce the relationships between tables. CREATE TABLE orderpart (ordernmbr int, partnmbr int FOREIGN KEY REFERENCES partsample(partnmbr) ON DELETE NO ACTION, qtyordered int); GO

Constraints can be column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column. The constraints in the previous examples are column constraints. A table constraint is declared independently from a column definition and can apply to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint. CREATE TABLE factoryprocess (eventtype int, eventtime datetime, eventsite char(50), eventdesc char(1024), CONSTRAINT eventkey PRIMARY KEY (eventtype, eventtime) )

Ref- BOL

Constraints define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.

1. NOT NULL specifies that the column does not accept NULL values.

2. CHECK constraints enforce domain integrity by limiting the values that can be put in a column

CREATE TABLE citydata ( cityid int PRIMARY KEY, cityname varchar(50) CONSTRAINT chkid CHECK (city_id BETWEEN 1 and 200 ) )

3. UNIQUE constraints enforce the uniqueness of the values in a set of columns. In a UNIQUE constraint, no two rows in the table can have the same value for the columns

4. PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table

CREATE TABLE citydata (cityid int PRIMARY KEY, city_name varchar(50));

5. FOREIGN KEY constraints identify and enforce the relationships between tables. A foreign key in one table points to a candidate key in another table.