Home > DeveloperSection > Interviews > What are the different types of Constraints in SQL?

Posted on    September-27-2016 1:19 AM

 MSSQL Server SQL Server  SQL 
Ratings:
 1 Answer(s)
  113  View(s)
Rate this:

Abhishek Srivasatava
Abhishek Srivasatava

Total Post:70

Points:350
Posted on    September-27-2016 1:19 AM

Constraint:

Constraint means limitation. As the name suggests it used to limit something. In SQL server constraints are used to limit the type of data that need to insert or update in the table.

In technical term Constraints can be rule that could be table level or column level.


Following are the some of the Constraints that are commonly used in SQL server :


1: Not Null: It Ensure that a column can't be empty.

Example: CREATE TABLE
EE_EMPL_CAT
(PRSN_INTN_ID int NOT NULL,
EMPL_STAT_CD char (20) NOT NULL,
NAME char (10),
REFERENCE_ID int,
);


2: Default: It provide the default value, if there is nothing specified.


Example: ( PRSN_INTN_ID  int NOT NULL ,
EMPL_STAT_CD char (20)  NOT NULL ,
NAME char(10) DEFAULT XXXXX,
REFERENCE_ID int,
);


3: Unique: It ensures that all the values in the column are unique.


Example: CREATE TABLE
EE_EMPL_CAT
(PRSN_INTN_ID int NOT NULL UNIQUE ,
EMPL_STAT_CD char (20) NOT NULL ,
NAME char (10),
REFERENCE_ID int,
);


4: Primary Key: It is used to uniquely identified row in the database table.


Example: CREATE TABLE
EE_EMPL_CAT
(PRSN_INTN_ID int NOT NULL PRIMARY KEY ,
EMPL_STAT_CD char (20) NOT NULL,
NAME char (10),
REFERENCE_ID int,
);


5: FOREIGN KEY:  It is used to uniquely identified row in another database table.


Example: CREATE TABLE CHILD_PRSN
CHILD_ID INT NOT NULL,
CHILD_NAME CHAR (20),
PRSN_INTN_ID INTFOREIGN KEY REFERENCES  EE_EMPL_CAT (PRSN_INTN_ID),
)


6: Check: It ensure that all values in columns satisfy certain condition

Example: CREATE TABLE
EE_EMPL_CAT
( PRSN_INTN_ID  int NOT NULL CHECK PRSN_INTN_ID >0,
EMPL_STAT_CD char(20)  NOT NULL ,
NAME  char(10),
REFERENCE_ID int,
);

 

7: Index: It is used to create or retrieve data from the database very quickly.


CREATE INDEX PIndex
ON EE_EMPL_CAT(PRSN_INTN_ID, NAME)

 

Constraint is specified while creating a table or we can use the keyword ALTER TABLE statement to create constraint even after the table is created.

 

8: Dropping Constraints:


We can drop any constraint whatever we have define by using the ALTER TABEL with DROP CONSTRAINT.

E.g. ALTER TABLE EE_EMPL_CAT DROP CONSTRAINT PRSN_INTN_ID;

We can use the shortcut for dropping the constraint by using keyword like PRIMARY key,

E.g. ALTER TABLE EE_EMPL_CAT DROP PRIMARY KEY;

 

Note:  PRIMARY KEY and UNIQUE can't be define for single column.


Modified On Sep-27-2016 01:20:53 AM

Don't want to miss updates? Please click the below button!

Follow MindStick