What are the different types of Constraints in SQL?

Question

Abhishek Srivasatava · Answer

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:and ( PRSN_INTN_IDand  int NOT NULL ,EMPL_STAT_CD char (20)and  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:and  It is used to uniquely identified row in another database table.
Example:and CREATE TABLE CHILD_PRSNCHILD_ID INT NOT NULL,CHILD_NAME CHAR (20),PRSN_INTN_ID INTFOREIGN
KEY REFERENCESand  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_IDand  int NOT NULL CHECK PRSN_INTN_ID andgt;0,EMPL_STAT_CD char(20)and  NOT NULL ,NAMEand  char(10),REFERENCE_ID int,);
and 
7: Index: It is used to create or retrieve data from the database very quickly.
CREATE INDEX PIndexON
EE_EMPL_CAT(PRSN_INTN_ID, NAME)
and Constraint is specified while creating a table or we can use the keyword ALTERTABLE statement to create constraint even after the table is created.and 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;and Note:and  PRIMARY KEY and UNIQUE can't be define for single column.

interview

What are the different types of Constraints in SQL?

Abhishek Srivasatava

Can you answer this question?

1 Answers

Liked By