Data Management

TechRepublic Tutorial: Enforce data integrity with constraints and Enterprise Manager

Data integrity is essential to the effectiveness of a Microsoft SQL Server database. Enforcing data integrity is achieved with data constraints, which can be implemented through the Enterprise Manager client.

Ensuring the correctness of all data contained within a database (data integrity) is a difficult and sometimes impossible chore, but it is an essential goal for the system. After all, how can the data be properly utilized if the data is inconsistent? When we create applications, we develop the code in such a way to check for valid data values, but SQL Server also allows the database server to be the gatekeeper for data integrity by utilizing constraints. In addition to constraints, SQL Server provides the Enterprise Manager client to simplify the process.

What is valid?
Developing a database application requires the knowledge of what type or range of values may be stored in a particular table. Once this is determined, the process of enforcing these rules must be determined. The enforcement may be handled with constraints, default values, and null values support.

The following list outlines each constraint type as well as default and null values:
  • PRIMARY KEY—Most often a table has a column or combination of columns whose values uniquely identify each row in the table. This is called the primary key of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table. A table may contain only one primary key.
  • FOREIGN KEY—A FOREIGN KEY (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table.
  • UNIQUE—You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key.
  • CHECK—The CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are placed in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints get the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column.
  • DEFAULT—The Default constraint defines values that are stored to a column when no value has been explicitly assigned.
  • Nullability—This determines whether a column accepts NULL values or not.

Utilizing each design feature in your database application allows you to control data integrity and enjoy a good night’s rest knowing data is valid and consistent. These features may be utilized via Transact-SQL or using the Enterprise Manager client.

Enterprise Manager
The Enterprise Manager client provides a graphical interface for interacting with existing databases or designing new ones. I will use the standard pubs database (available with all basic SQL Server installations) for the purpose of demonstration. Constraint definitions are accessed by viewing a table design and right-clicking on a particular column. In Figure A, I have selected the discount column contained in the discounts table of the pub database. By right-clicking, I have the options presented in Figure A.

Figure A
Working with constraints in Enterprise Manager

Here is a brief overview of some of the options:
  • Set Primary Key—Establish the selected column as the table’s primary key. In Figure A, the discounttype field is the primary key. The key to the left of the column name makes this clearly visible.
  • Relationships—Establish foreign key relationships between the selected column and a column in another table.
  • Check Constraints—Create check constraints for the table. Also, edit and delete other check constraint definitions. Figure B shows the creation of a check constraint on the discount column (must be greater than 0).

Figure B
Creating check constraints

Other options that you should notice in Figure B are the three checkboxes at the bottom of the window. They allow you to specify whether existing data is subjected to the new constraint, whether the constraint is used on both inserts and update operations, and if it is enforced on replication. Creating a new constraint with existing data is often problematic, since the older data does not always conform to the rule. For this reason, the option to bypass existing data (uncheck the box) is used. Of course, this negates data integrity so a better option is massaging the current data so it conforms to the new rules.

Figure C shows the relationships defined for the selected table. The figure shows a foreign key relationship between the stores table and the selected discounts table. The actual foreign key is in the discounts table, and it points to the primary key of the stores table. This relationship may be edited or deleted. In addition, new relationships may be added. There are options similar to the constraints options that allow you to specify whether the relationship is enforced upon inserts, updates, and replication. Also, the option to set up cascading updates and/or deletes is available.

Figure C
Working with relationships

Default values may be assigned in the design table interface. In Figure A, the default value option is available for the selected column in the bottom portion of the screen. In addition, the Allow Nulls column allows you to check or uncheck to determine if the column allows null values to be stored within it.

One of the many features
Designing a database system is an arduous task. Once the table structures have been defined, data integrity must be ensured. This process is aided by using constraints, and SQL Server simplifies the process by providing a graphical interface with its Enterprise Manager client.

Editor's Picks