SQL Server Data integrity

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)