Constraints

Constraint	Description
Primary Key	- Set of one or multiple columns which will uniquely identify each row in the table - All columns should be NOT NULL - Only 1 PK is allowed - By default Clustered index will be created. - You can create Nonclusted index like this. CREATE TABLE pkcheck (a int not null) ALTER TABLE pkcheck ADD CONSTRAINT pk_pkcheck PRIMARY KEY NONCLUSTERED (a)
Foreign Key	- Set of one or multiple columns which are being used to implement the referential integrity - Can be null - Parent table should have a primary key which is being used as FK in child table - datatype should match in Primary and Child table CONSTRAINT FK FOREIGN KEY (a,b) REFERENCES PK(c,d) - ON DELETE CASCADE option will delete from child table automatically whenever deleted from parent table
Unique	- Set of one of multiple columns which will uniquely identify each row in the table - will automatically create NONCLUSTERD index - If NULL values are there, only one NULL value will be allowed
Default	- Allows to specify a default value whenever a value is not passed in INSERT statement - can be applied on NULL and NOT NULL columns CREATE TABLE def (a INT) ALTER TABLE def ADD CONSTRAINT def_a DEFAULT(0) FOR a
Check	- allows to specify the range - ALTER TABLE test.OrderHeader ADD CONSTRAINT ck_subtotal CHECK (SubTotal > 0)