what is Information Integrity & it is categories in sqlserver 2000?

Sponsored Links:

Enforcing information integrity ensures the quality of the information in the database. For example, if an employee is entered with an employee_id value of 123, the database should not permit another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should permit only values that are valid for the department numbers in the company. Six important steps in planning tables are to identify valid values for a column and to choose how to enforce the integrity of the information in the column. Information integrity falls in to these categories:

1) Entity integrity
2)Domain integrity
3) Referential integrity
4) User-defined 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).

Domain Integrity:
Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through information types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT efinitions, NOT NULL definitions, and rules).

Referential Integrity:
Integrity User-defined integrity lets you define specific business rules that do not fall in to six 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).

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, 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). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it alter consistently throughout the database. When you enforce referential integrity,

SQL Server prevents users from:
· Adding records to a related table if there is no associated record in the primary table.
· Changing values in a primary table that result in orphaned records in a related table.
· Deleting records from a primary table if there's matching related records.
For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.
User-Defined: