Data Integrity

By Roger Stuart


Enforcing data integrity ensures the quality of data in a database. For example, if a product is entered with a Product_ID value of 25 in a table named Products, the database should not allow another product to have an ID with the same value. Furthermore, if there is a column named Product_Rating that is intended to have values ranging from 1 to 10, the database should not accept a value below 1 and above 10 for this column. This can be accomplished by using the methods supported by SQL Server to enforce the integrity of the data.

SQL Server supports a number of methods that can be used to enforce data integrity. These methods include defining datatypes, NOT NULL definitions, DEFAULT definitions, IDENTITY properties, rules, constraints, triggers, and indexes.

Datatypes

A datatype is an attribute that specifies the type of data (e.g., character, integer, binary, etc.) that can be stored in a column, parameter, or variable. SQL Server provides a set of system-supplied datatypes. However, users can also create user-defined datatypes based on the system-supplied datatypes. System-supplied datatypes define all of the types of data that can be used with SQL Server. Datatypes can be used to enforce data integrity because the data entered or modified must conform to the type specified for the object. For example, a name cannot be stored in a column defined with the datetime datatype, as a datetime column can accept only date values.

NOT NULL Definitions

The nullability of a table column determines whether the rows in the table can contain a null value for that column. A null value in a column does not mean that the column has zero, blank, or a zero-length character string such as " ". Null in a column means that no data has been entered in that column. The presence of a null in a column implies that the value is either unknown or undefined.

The nullability of a column is defined while defining the column or while creating or modifying a table. The NULL keyword is used to specify that the column will allow null values. The NOT NULL keyword specifies that null values will not be allowed in the column.

DEFAULT Definitions

Each column in a row must contain a value even if that value is null. However, certain situations exist when a row is inserted in a table, but the value for a column is not known or the value does not yet exist. If the column allows null values, a row with a null value for that column can be inserted in the table. In some cases, nullable columns might not be desirable. In these cases, a DEFAULT definition can be defined for the column. Defaults specify what values are automatically inserted in a column if a value is not specified for the column when inserting a row in the table. For example, it is common to specify zero as the default for numeric columns and N/A as the default for string columns.

When a row is inserted in a table with a default definition for a column, the SQL Server is implicitly instructed to insert the specified default value in the column if a value is not specified for the column.

IDENTITY Properties

The IDENTITY property is used to define a column as an identifier column. An identifier column contains system-generated sequential values that uniquely identify each row in the table. A table can have only one identifier column. Identifier columns usually contain values that are unique only within the table for which they have been defined. In other words, other tables containing identifier columns can contain the same identity values used by another table. However, the identifier values are typically used only within the context of a single table, and the identifier columns do not relate to other identifier columns in other tables.

Constraints

Constraints are used to define the way that SQL Server automatically enforces the integrity of a database. A constraint is a property assigned to a table or column within a table that prevents invalid data values from being entered in the specified column(s). For example, a PRIMARY KEY or UNIQUE constraint on a column prevents a duplicate value from being inserted into the column. A CHECK constraint on a column prevents the column from accepting a value that does not meet the specified condition. Moreover, a FOREIGN KEY constraint establishes a link between data in two tables.

Rules

Rules perform some of the same functions as CHECK constraints. However, CHECK constraints are preferred over rules. Rules are provided only for backward compatibility. CHECK constraints are more concise than rules. A column can have only one rule applied to it. However, multiple CHECK constraints can be applied to a column. CHECK constraints are specified while creating a table, whereas rules are created as separate objects and are bound to the column.

The CREATE RULE statement is used to create a rule. Once a rule has been created, it can be bound to a column or a user-defined data type by using the sp_bindrule system stored procedure.

Triggers

Triggers are special types of stored procedures that are defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers can be used to enforce business rules automatically when data is modified. Triggers can also be used to extend the integrity checking logic of constraints, defaults, and rules. However, it is recommended that constraints and defaults be used instead of triggers whenever they provide all of the needed functionality.

Indexes

An index is a database object that orders the values of one or more columns in a table. An index provides pointers to the data values stored in specified columns of the table and orders the pointers in the specified order. 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.

Types of Data Integrity

SQL Server supports the following four types of data integrity:

1.Entity 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).

2.Domain Integrity

Domain integrity validates the entries for a given column. Domain integrity can be enforced by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY and CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

3.Referential Integrity

Referential integrity maintains the defined relationship between tables when records are entered or deleted from the tables. In 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 the related tables.When referential integrity is enforced, SQL Server prevents users from adding records to a related table if there is no associated record in the primary table. Users are also prevented from changing values in a primary table or deleting records from the primary table if there are related records in the related table.

4.User-Defined Integrity

User-defined integrity is used to define specific business rules that do not fall into any of the other integrity categories. All of the integrity categories support user-defined integrity. All column-level and table-level constraints defined in CREATE TABLE, stored procedures, and triggers are examples of user-defined integrity.


More Resources

Unable to open RSS Feed $XMLfilename with error HTTP ERROR: 404, exiting

More Data Recovery Information:

Related Articles

Do I Really Need To Backup?
Okay, computers a machine, right? Okay, maybe not yours. Youhave this special relationship, but that a subject foranother article.
Computer Data Backups: Test Now or Cry Later
If you're like most small business owners, your computer data backups are one of those things that you rarely pay attention to. Computer data backups are kind of like flossing your teeth and eating low-fat, high-fiber foods? everyone knows what they're supposed to do? but how many REALLY do these things religiously?!?Unfortunately when it comes to your computer data backups however, complacency can be very dangerous.
Computer Data Recovery Options
Losing files on your computer can be a frightening experience if the files are of importance to you personally or professionally. Computer data recovery techniques include the most simple of operations, like simply retrieving an accidentally deleted file, to very complex file retrieval procedures that only professionals should attempt to do.
Data Recovery Processes
Six months ago my computer crashed. When I turned my computer on it would not load Windows XP and so I could not get into my two hard drive files.
Business Continuity and Disaster Recovery - A Business Not a Technology Issue
Hackers, hurricanes, fires, flooding, power outages, denial of service attacks, application failures, employee error, sabotage and now terrorism are helping companies to focus on the necessity of a business continuity plan.Through the late 1990s as companies prepared for Y2K, many IT executives, risk managers, CFOs and corporate managers realized that recovering computing systems, networks and data was not enough.
Big Time Disaster Recovery Solutions Available for the Little Guy
There was a time - not too long ago - that Data Disaster Recovery solutions were available only in the domain of multinational corporations and big business in general. However, with the proliferation of high speed internet access and the ability to store large amounts of data in a much more cost effective manner due to plummeting storage costs, the ability to perform a secure backup of data to a remote location engineered specifically for the purpose of securely storing massive amounts of information is now in the reach of the general public.
How to Survive an Operating System Crash
"We apologize for the inconvenience, but Windows could not be accessed or located."It happens to the best of us.
Read This Now: Its Not If You Will Lose Your Data, Its When Will You Lose Your Data
With Adware, SpyWare, E-Mail Viruses, Java Script Viruses and Hardware failures It isn't a matter of If you will loose your Data it is a matter of When you will loose your Data! By spending a few extra minutes now using a Simple Back Up Strategy, you can save yourself big headaches later.You Should Always have a Full Backup of your Online Business Directory and all it's subdirectories for the Last 4 Weeks as well daily incremental backups for a month or More.
Online Data Backups for Newbies
How long have you been doing business online? Whether you are new or an old timer you need to understand the one important truth of the e-world. That being that the largest threat to business survival is data loss.
The Importance of Data Backups
All computers and their components are subject to failure. In fact sooner or later every business will be confronted with some type of computer failure.
Data Recovery - What Not to Do!
Data recovery is a tricky thing, and if you've somehowdeleted or had your important files corrupted or lost due tohuman error, business espionage, faulty hardware or softwareor any other reason; the good news is that your lost datais probably recoverable. This article will show you a fewthings NOT to do when an event such as this occurs.
Sea-Front or Action-gate
June 25, 2005Seagate Technologies' web site now indicates the company offers data recovery services. Further investigation shows the shipping address, the place to send your subject drive for data recovery service, actually belongs to ActionFront Data Recovery.
Bill Gates Made Data Recovery Easy
Bill gates provided a ready made option in Microsoft windows, which is very useful for data recovery.Several time we make mistakes unknowingly and we lost our data, then we need to recover the data.
Backup Your Data Or Lose Your Life!
Oh the perils of collecting those precious photos on your PC for years, only to have your hard drive crash one day, and not have made a SINGLE BACKUP COPY of any of your priceless pictures. Well, now is the time to backup your hard drive.
When Disaster Strikes: How Long Would Your Business Survive if Files On Your Computer Disappeared?
A Tornado? In Birmingham? In The United Kingdom? Unthinkable!Yes, in July 2005 (while I was putting finishing touches to my cave-house in Spain) my house, along with many of my neighbours' houses was struck by a powerful tornado that literally tore up the streets. Leaving people homeless as their homes were bulldozered (with all their possessions inside).
Computer Data Backup - Data Backup Solution Will Give You A Peace Of Mind
Imagine that you have been working on a file for the entire day and there was a power surge that caused your computer to 'black out'. It would have been a frustrating experience for us as we had put in a lot of time and effort into it.
Business Continuity and Disaster Recovery - Risk Analysis and Control
In the risk evaluation phase, there are a number of key areas that must be covered. One of the most important is to understand probable threats.
The Importance Of Email Backup
Viruses, software failures, power failures, human errors, hard drive failures are only a few examples of what could destroy the data on a hard drive, including all documents, pictures, emails and other files!Most home computer users don't need an expensive backup solution; as they only need to burn the folders with important documents and pictures to a CD-RW from time to time; this ensuring that in case of a disaster they can easily get them back. Togeder with documents and pictures, it's a critical operation to save the emails, attachments, address book and other important data from within the email client; otherwise in case of a computer problem the user will find himself in the impossibility to get them back, and this can be a really unwanted situation especially for webmasters or people who rely on their emails.
How To Limit Hard Drive Data Recovery Costs
With the value on information rising day by day, one of thegreatest threats to businesses of any size is data loss. If thedata centers in your business have ever experienced a hard drivedisaster, you quicky realized just how valuable the informationlost is.
Data Recovery The Easy Way
If you aren't prepared in advance, you will most likely have to use a data recovery service or data recovery software solution to help get back as much as you can of what was lost. This can be a very good idea if there is no other way to recover your files, but it doesn't have to come to that in most cases.