SQL Server Interview Day - 3
1) What is constraints in SQL server ?
- Constraint is rule or restriction imposed and individual column such that unwanted data can't be inserted into tables.
- We can create constraints on single or multiple columns of any table.
- Constraints maintain the data integrity and accuracy in the table.
- Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
2) How many types of constraints in SQL server ?
Constraints are divided into many type. Please follow the below details.
NOT NULL
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
UNIQUE
3) What is NOT NULL constraint ?
It will restricted to NULL value.That means It will not allow the NULL value at record insert/update time.
Example:-
CREATE TABLE tbl_Employee (
ID int NOT NULL,
EmployeeName varchar(200) NOT NULL,
CompanyWebSite varchar(300)
);
Record 1:-
INSERT INTO tbl_Employee (ID, EmployeeName, CompanyWebSite) VALUES ( 1, 'Lipsa','
www.tutorialslife.com')
Above record will inserted successfully.
Record 2:-
INSERT INTO tbl_Employee (ID, EmployeeName, CompanyWebSite) VALUES ( 1, NULL,'
www.tutorialslife.com')
Above record will not insert into the employee table.
4) What is a PRIMARY KEY ?
- This is constraint which in forces not null value and no report value in a column using this constraint the record is unique identify.
- It will not allow to insert/update null value
- It will also not allow to insert/update duplicate value
Example:-
CREATE TABLE tbl_Employee (
ID int PRIMARY KEY,
EmployeeName varchar(200) NOT NULL,
CompanyWebSite varchar(300)
);
Record 1:-
INSERT INTO tbl_Employee (ID, EmployeeName, CompanyWebSite) VALUES ( 1, 'Lipsa', '
www.tutorialslife.com')
Above record will inserted successfully.
Record 2:-
INSERT INTO tbl_Employee (ID, EmployeeName,CompanyWebSite) VALUES ( 1, 'Sriya', '
www.tutorialslife.com')
Above record will not insert into the employee table because employee ID is duplicate with old value or employee ID "1" already available on the table.
Record 3:-
INSERT INTO tbl_Employee (ID, EmployeeName, CompanyWebSite) VALUES ( NULL, 'Lion', '
www.tutorialslife.com')
Above record will not insert into the employee table because employee ID is NULL value.
5) What is a FOREIGN KEY ?
- FOREIGN KEY constraints which helpful up to make relationship between two or more than two table using a common attribute.
- It always refers to same table primary key or another table primary key to make a relationship.
- It is just opposite of primary key that means it can accept duplicate values and null.
- It supports the concept of referential integrity.
Example:-
CREATE TABLE tbl_Department (
DepartID int NOT NULL,
DepartName varchar(200),
DepartLoc varchar(300),
);
Record 1:-
INSERT INTO tbl_Department (DepartID, DepartName, DepartLoc) VALUES ( 1, 'Training Department','Bangalore')
Above record will inserted successfully.
Record 2:-
INSERT INTO tbl_Department (DepartID, DepartName, DepartLoc) VALUES ( 2, 'Account Department','New Delhi')
Above record will inserted successfully.
CREATE TABLE tbl_Employee (
ID int PRIMARY KEY,
EmployeeName varchar(200) NOT NULL,
CompanyWebSite varchar(300),
DepartID int,
CONSTRAINT FK_tbl_Employee_tbl_Department FOREIGN KEY (DepartID) REFERENCES tbl_Department(DepartID)
);
Record 1:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite, DepartID) VALUES ( 1, 'Lipsa','
www.tutorialslife.com',1)
Above record will inserted successfully.
Record 2:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite, DepartID) VALUES ( 1, 'Sriya','
www.tutorialslife.com',NULL)
Above record will inserted successfully.
Record 3:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite, DepartID) VALUES ( NULL, 'Lion','
www.tutorialslife.com',5)
Above record will not insert into the employee table because Department 5 is not available.
6) What is a CHECK constraint ?
- CHECK constraint is used for check a condition.
- CHECK constraint must be specified as a logical expression that evaluated specific condition either TRUE or FALSE.
- specific condition should be satisfies all values in a column.
Example:-
CREATE TABLE tbl_Employee (
ID int PRIMARY KEY,
EmployeeName varchar(200) NOT NULL,
CompanyWebSite varchar(300),
Age int CHECK (Age>=20)
);
Record 1:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite, Age) VALUES ( 1, 'Lipsa','
www.tutorialslife.com', 24)
Above record will inserted successfully.
Record 2:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite, Age) VALUES ( 2, 'Lion','
www.tutorialslife.com', 16)
Above record will not insert into the employee table because age value is less then 18.
7) What is a DEFAULT constraint ?
- The DEFAULT constraint is used to provide a default value for a attribute.
- When you inserting data does not provide any specific value automatically assign default value only if you specified DEFAULT constraint.
- DEFAULT constraint only impose at column level.
Example:-
CREATE TABLE tbl_Employee (
ID int PRIMARY KEY,
EmployeeName varchar(200) NOT NULL,
CompanyWebSite varchar(300) DEFAULT '
www.tutorialslife.com'
);
Record 1:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite) VALUES ( 1, 'Lipsa', '
www.tutorialslife.com')
Above record will inserted successfully.
Record 2:-
INSERT INTO tbl_Employee (ID,EmployeeName) VALUES ( 2, 'Lion')
Above record will inserted successfully. It will insert '
www.tutorialslife.com' value for CompanyWebSite column.
8) What is a UNIQUE constraint ?
- Using this constraint no duplicate value is allow in column but accepts null.
- It will not allow duplicate value on the column.
- It will allow null value only one time.
Example:-
CREATE TABLE tbl_Employee (
ID int PRIMARY KEY,
EmployeeName varchar(200) UNIQUE,
CompanyWebSite varchar(300)
);
Record 1:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite) VALUES ( 1, 'Lipsa','
www.tutorialslife.com')
Above record will inserted successfully.
Record 2:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite) VALUES ( 2, 'Lipsa','
www.tutorialslife.com')
Above record will not insert into the employee table because employee name is duplicate with old value.
Record 3:-
INSERT INTO tbl_Employee (ID,EmployeeName,CompanyWebSite) VALUES ( 3, NULL,'
www.tutorialslife.com')
Above record will inserted successfully.
9) What is referential integrity ?
Referential integrity is a concept of relational database, It states that table relationships must between tables in a database remain accurate by applying constraints to prevent users or applications from entering inaccurate data or pointing to data that doesn't exist.
10) What are difference between Table level and Column level constraints?
Table level constraints:-
Table level constraints are those constraints which are imposed end of the column declaration during table deign.
Table level constraints can able to create one or more then one constraint is imposed at one point of time
Table level constraints is syntactically not clear as compare to Column level constraints
Column level constraints:-
Column level constraints are those constraints which are imposed in just after column rectification during table deign.
Column level constraints maximum one constraint is imposed at one point of time
Column level constraints is syntactically more clear.
Please click on this link for more details about constraints.