SQL Server Data integrity interview questions
SQL Server data integrity - Nov 18, 2008 at
18:00 PM by Rajmeet Ghai
What are ways to ensure integrity of data?
Answer
Data integrity ensures quality of data. It helps keep the data
unchanged and unique.
-
Data types
ensure that the data accepted by the column is restricted to the type
specified. For e.g. an integer data type cannot accept strings.
-
NOT NULL
definition ensures that the column does not accept a NULL value. This means
that the data cannot be empty for this column.
-
Default
definition means that the value is set to a value by default unless specified.
For e.g. ‘0’ is used for integer data types.
-
Identity
property is used to define a column as an identifier. This column contains
system generated values that are sequential. They help to uniquely identify the
row.
-
A constraint
helps invalid data to be entered. For e.g. a FOREIGN KEY. CHECK can be used to
ensure the column accepts the specified data.
-
Rules
are similar to CHECK, but used for backward compatibility. They help to
restrict acceptance of specified data.
-
A trigger
is a set of code which gets executed on an INSERT, UPDATE or DELETE on some
table. Triggers can be used to enforce business rules automatically when data
is modified.
-
Constants
ensure the data value does not change and thus maintaining data integrity.
-
An index ensures that when rows are requested from an indexed
table, the database searches the index to find a particular value and then
follows the pointer to the row containing that value.
Explain the types of Data integrity.
Answer
a. Entity Integrity: It means that the row is kept as a unique
entity. It ensures that the integrity of the identifier is maintained through
PRIMARY KEYS, IDENTITY property etc
b. Domain Integrity: It validates the entries for a given
column. Restricted data types can be given, FOREIGN KEY, CHECK and NOT NULL
constraints can be specified.
c. Referential Integrity: it helps in maintaining the
relationships between tables when a new row is inserted or deleted. Usually
Foreign leys are used to define relationships between tables.
d. User-Defined Integrity: as the name suggests, user defined
integrity is used to define business rules by the user. The rules are such that
they don’t fall under the category of any type of integrity type. Procedures
and triggers are examples of such types of user – defined integrity.
What are integrity constraints? Explain their types. i.e. column and table
constraints.
Answer
Integrity constraints: Integrity constraints ensure data
consistency. This is achieved by making sure that changes made to the database
by authorized users keep the data consistent. These constraints allow data to
be inserted /modified only if it meets specified criteria.
Column Level Constraints: These constraints are applied at the
column level. NOT NULL constraint is an example of this constraint. This
constraint means that the column specified as NOT NULL cannot accept an empty
string as a value. E.g. Column_name NOT NULL
Table Level Constraints: These constraints are specified
independently on more than TWO column of a table. If the need is such that TWO
or more than TWO columns need to have a constraint; they fall under the table
level constraints. For e.g a table can have more than two or more columns in
the primary key.
Explain the classes of constraints. i.e. Primary key, Unique, Foreign Key and
Check Constraints.
Answer
Primary Key: This constraint when applied to a column ensures that
the row is unique. This means that only ONE row with this ID can exist. For
e.g. a table SALES has its primary key as sales_id. The column is specified as
primary key by :sales_id PRIMARY KEY
Unique: This constraint makes sure that the columns have unique
values. This also means that no two rows can have the same value for the column
specified as unique. They differ from primary keys by allowing NULL as one of
the values. The keyword UNIQUE is used.
Foreign Key: This constraint helps to establish relationships
between tables. When one key is being referenced in another table, the key is
called as a foreign key. For e.g. Sales_id from SALES table can be referenced
in ORDER table. E.g in table ORDER sales_id is written as: FOREIGN KEY
REFERENCES SALES (sales_id)
Check constraints: This constraint restricts the value that can
be inserted In a column. It specifies a BOOLEAN value. Values that can be
accepted and meet the criteria are evaluated as TRUE while the ones that are
rejected are evaluated as FALSE.
e.g. CONSTRAINT amt CHECK (amt BETWEEN 10 and 1000)
<<Previous
Next>>
|