Working with SQL Server 2005 – SQL 2005 Tutorial – Learn SQL Part 1

Welcome to the course Working with SQL Server Database Objects. This course aims to teach advanced features of SQL Server 2005 such as Indexes, Stored Procedures, Triggers and Views.

Data Integrity

In a database when data is entered or modified, the entered values need to be valid and accurate. Also, if any modification or deletion is carried out on the data, the change should reflect in all the relevant places in the database. This ensures the validity and consistency of data at all times, hence maintaining data integrity. Integrity of data can be maintained by specifying certain checks at the time of creating and modifying tables and then applying those checks when handling data.

Data integrity needs to be maintained for data to be useful. You need to ensure the correctness and consistency of the data at all times. There are certain rules that help in maintaining the accuracy and consistency of data. These are:

• No two records in a table can have the exact same values in all the columns.
• Only valid data values can be inserted.
• The validity of data has to be maintained when data is modified.

When multiple tables are related through a common column, any changes to the data values in that column in one table should be appropriately reflected in the related tables.

Business Rules

In addition to the general rules for maintaining data integrity, you can also incorporate certain business rules specific to your organization.

Business rules are the policies and standards adhered to by an organization in running its operations. These rules ensure that the data entered in the database conforms to company standards. For example, the business rules of a bank can specify the maximum amount of loan and the maximum duration for loan repayment based on a person’s education level, current annual income and age.

Enforcing Data Integrity

The rules for data integrity can be enforced by subjecting the data to various checks and constraints at the time of inserting, updating and deleting data. These checks and constraints are specified in the column or table definition at the time of creating or modifying a table.

SQL Server 2005 supports four mechanisms that allow you to enforce data integrity. These are:

• Constraints

Constraints are properties you can assign to columns in a table to prevent invalid data from being entered into the columns.

• Default Values

You can define default values for columns which do not accept null values. These default values will be inserted in records where these columns are left blank during insertion or modification.

• Rules

Rules are constraints that can be applied in order to control the data values being entered in a table. Rules are independent of table definitions and can be applied to multiple tables.

• Triggers

A trigger in SQL Server 2005 contains a piece of code made up of Transact-SQL statements that is automatically executed when specified events occur.

Test Your Knowledge

Which of these statements about the mechanisms supported by SQL Server to enforce data integrity are true and which statements are false?

1. Constraints assist in ensuring data integrity.
2. Triggers are values that you can define on a column or a group of columns.
3. Rules are the constraints that you can apply to multiple tables.
4. Rules are independent of table definitions.
5. Defaults are the codes automatically executed when a specified event occurs.

Answer

1. True
2. False
3. True
4. True
5. False

Read: Top 50 General Quiz about Animals All Around the World