SQL Server interview questions on data integrity constraints
In this article I will share some frequently asked data integrity constraints related interview questions and answers in SQL server.
1. What is table constraint?
Constraints are used to specify rules on data
to ensure quality data stored in table.
2. What are the Different Types of constraints?
=> Primary key constraint.
=> Foreign Key constraint.
=> Unique Key constraint.
=> Not Null constraint.
=> Check constraint
=> Default constraint
3. What is default constraint?
The DEFAULT constraint provides a
default value to a column when an INSERT statement doesn't explicitly assign a
particular value.
More info.. http://codechef4u.com/post/2015/05/04/default-constraint-in-sql-server
4. Explain primary key and foreign key constraints?
Foreign key is a column or field
in current table and primary key of another table, the foreign key points to
another table using primary key of that table.
The PRIMARY KEY constraint
uniquely identifies each record in a database table; main goal is to prevent
duplicate values for columns and provides unique identifier to each column.
More info.. http://codechef4u.com/post/2015/04/20/how-to-create-primary-key-constraint
http://codechef4u.com/post/2015/05/03/foreign-key-constraints
5. Explain PRIMARY KEY, FOREIGN KEY and Unique key?
PRIMARY KEY:
The PRIMARY KEY uniquely
identifies each record in a database table; main goal is to prevent duplicate
values for columns and provides unique identifier to each column.
FOREIGN KEY:
Foreign key is a column or field
in current table and primary key of another table, the foreign key points to
another table using primary key of that table.
Unique key:
Unique kay enforces uniqueness or
prevent duplicate data on columns.
Single null value is allowed with
unique key.
6. What is UNIQUE KEY Constraint?
Unique constraints are rules that enforce uniqueness or
prevent duplication on column data.
More info.. http://codechef4u.com/post/2015/05/05/unique-constraint-in-sql-server
7. What is CHECK Constraint?
CHECK
constraints are rules used to limit values that are accepted by one or more
columns.
For example, Products with unit
price between INR 5000 to INR 100000 are allowed only. This prevents product
unit price from being entered beyond the regular price range.
The logical
expression would be the following: UnitPrice>= 5000 AND UnitPrice <= 100000
More info.. http://codechef4u.com/post/2015/04/24/check-constraints-in-sql-server
8. What is NOT NULL Constraint?
9. What is the difference between primary key and unique key?
Primary Key:
Primary Key can't accept null
values.
By default, Primary key is
clustered index.
We can have only one Primary key
in a table
Unique Key:
Unique key can accept only one
null value.
We can have more than one unique
key in a table.
Unique key is a unique
non-clustered index.
10. What is data integrity and list
types of data intigrity?
Data integrity
is the consistency and accuracy of data which is stored in database.
For data integrity
RDBMS follows ACID rules, enforcing data integrity ensures the quality of the
data in the database.
Types:
Entity Integrity
(Unique constraints, primary key)
Domain Integrity
(Check constraints, null constraints, default constraints, foreign key)
Referential Integrity
(Foreign key and check constraints)
User-Defined
Integrity (all column- and table-level constraints in CREATE TABLE, stored
procedures, and triggers).
11. Can you add constraint on table
that has already some data?
Yes we can,
you require to modify data in some cases for example if you want to add null constraint
and
Table contains
some null value in that’s case you have to add some data in column.
12. What is User-Defined Integrity?
User-defined
integrity allows you to define specific business rules that do not fall into
one of the other integrity categories. All of the integrity categories support
user-defined integrity (all column- and table-level constraints in CREATE
TABLE, stored procedures, and triggers).
13. What is referential Integrity?
Referential
integrity preserves the defined relationships between tables when records are
entered or deleted. In SQl server, referential integrity is based on
relationships between foreign keys and primary keys or between foreign keys and
unique keys (through FOREIGN KEY and CHECK constraints).
14. What is domain Integrity?
Domain
integrity is the validity of entries for a given column. You can enforce domain
integrity by restricting the type (through data types), the format (through
CHECK constraints and rules), or the range of possible values (through FOREIGN
KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions,
and rules).
15. What is entity Integrity?
Entity
integrity defines a row as a unique entity for a particular table. Entity
integrity enforces the integrity of the identifier column(s) or the primary key
of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or
IDENTITY properties).
16. While creating foreign key you don’t
want to check referential integrity, which keyword you have to use?
You required
WITH NOCHECK in such case.
17. Can we apply unique constrains on
multiple columns?
Yes that is
possible.
18. How many primary keys are
possible with single table?
Only one.