Admin
Working with SQL Server 2005 – SQL 2005 Tutorial – Learn SQL Part 2
SQL Server 2005 Tutorial Part 2
Types of Data Integrity
A database needs to maintain data integrity so that the information derived from the data is accurate and up-to-date.
Integrity of data can be classified into the following categories:
- Entity Integrity
- Domain Integrity
- Referential Integrity
- User-defined Integrity
Entity Integrity
A table in a database represents an entity whereas each record within the table represents an instance of that entity.
A table is said to comply with entity integrity when no two rows in the table have the exact same values in all the columns. Entity integrity is ensured using:
PRIMARY K EY constraint
A column with a PRIMARY KEY constraint does not allow duplicate or null values to be inserted.
UNIQUE constraint
A column with a UNIQUE constraint does not allow duplicate values to be inserted. However, this column allows a null value to be inserted once.
Indexes
Indexes can be used to prevent duplicate values from being entered in a column to ensure uniqueness of data values.
IDENTITY Property
The IDENTITY property defines an identifier column in the table. An identifier column contains system-generated sequential values for every record inserted. Thus, all values in this column are unique.
Domain Integrity
A domain defines a logical set of values that make up the valid values in a column. To maintain domain integrity, all data values in a table should be from the specified domain. In SQL Server 2005, domain integrity is maintained using the following:
FOREIGN KEY constraint
A FOREIGN KEY column can either have a value that exists in the UNIQUE or PRIMARY KEY columns of the referenced table or it can have a null value.
CHECK constraints
A CHECK constraint specifies the range of valid data values that can be entered into a column.
DEFAULT definitions
DEFAULT definitions specify default values for columns that do not accept null values. If there is a record in which the value for such a column is not entered, the specified default value is automatically assigned to that column.
NOT NULL definitions
A NULL value means that the value is either unspecified or it is unknown. If a column is left blank, its value is taken as a NULL. The NOT NULL definition specifies that a column cannot accept NULL values.
Data Types
Valid data types can be specified for different columns. For example, if the data type of a column is defined as date, this column will accept only date values.
Rules
Rules are constraints that specify the valid data formats or range for values in a column. Rules are defined independent of the table definition and can be applied to multiple tables.
Referential Integrity
Referential integrity maintains consistency of data across tables that are related through common columns.
For example, consider two tables, Employee_Details and Payroll. The two tables are related through a common column EmployeelD. If an employee resigns and his/her details are deleted from the Employee_Details table, the record with the same EmployeelD should simultaneously be deleted from the Payroll table.
Rules to Ensure Referential Integrity
In SQL Server 2005, referential integrity is implemented using the concept of FOREIGN KEYS. FOREIGN KEY columns reference UNIQUE or PRIMARY KEY columns in other tables. Referential integrity is ensured by the following rules:
- Values can be inserted in a FOREIGN KEY column only, if similar values exist in the referenced UNIQUE or PRIMARY KEY column.
- If a value in the UNIQUE or PRIMARY KEY column is modified, similar modifications are carried out in the referring FOREIGN KEY columns.
- If a value in the UNIQUE or PRIMARY KEY column is deleted, deletions should be carried out in the referring FOREIGN KEY columns.
User-defined Integrity
Default integrity constraints provided by SQL Server 2005 may not be enough to ensure data values in a desired format or range. In such cases, special user-defined constraints can be applied to columns to maintain data integrity. User-defined integrity is said to be maintained if the data values comply with the format and range restrictions specified by the user-defined constraints.
For example, if phone numbers are expected in a format such as (123) 456-7890, a user-defined data type can be created to accept values only in this format.
Test Your Knowledge
Which of these statements about the types of data integrity in SQL Server 200S are true and which statements are false?
(1) A row in a table represents the instance of an entity.
(2) A domain defines the range of values for columns in a table.
(3) Referential integrity is implemented using UNIQUE constraint.
(4) User-defined integrity is maintained using default constraints.
(5) DEFAULT definitions specify default values for columns that do not accept null values.
Answers
(1) True
(2) True
(3) False
(4) False
(5) True
