ADO.NET - Lesson 5: Constraints in Data Entry

	Constraints in Data Entry

Introduction

A constraint in a database is a rule used to apply restrictions on what is allowed and what is not allowed in the application. To assist you in creating an effective database, the SQL provides various types of constraints you can apply to your table(s).

The Nullity of a Field

During data entry, users of your database will face fields that expect data. Sometimes, for one reason or another, data will not be available for a particular field. An example would be an MI (middle initial) field: some people have a middle initial, some others either don't have it or would not provide it to the user. This aspect can occur for any field of your table. Therefore, you should think of a way to deal with it.

A field is referred to as null when no data entry has been made to it:

Saying that a field is null doesn't mean that it contains 0 because 0 is a value.
Saying that a field is null doesn't mean that it is empty. A field being empty could mean that the user had deleted its content or that the field itself would not accept what the user was trying to enter into that field, but an empty field can have a value.

A field is referred to as null if there is no way of determining its value or its value is simply unknown. As you can see, it is not a good idea to have a null field in your table. As a database developer, it is your responsibility to always know with certainty the value held by each field of your table. Remember that even if a field is empty, you should know what value it is holding because being empty could certainly mean that the field has a value.

To solve the problem of null values, the SQL proposes one of two options: allow or not allow null values on a field. For a typical table, there are pieces of information that the user should make sure to enter; otherwise, her data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, you must make sure the field doesn't allow null values; this will ensure that you know that the field is holding a value and, eventually, you can find out what that value is.

To apply nullity rules in SQL Server Enterprise Manager or Server Explorer, first display the table in the design view. To get it, in the SQL Server Enterprise Manager, you can right-click the table and click Design Table. In the Server Explorer of Microsoft Visual Studio .NET, you can right-click the table and click Design Table.

Once in the Design Table window, you can click or clear the Allow Nulls check box that corresponds to the column. On the other hand, if the value of a field is not particularly important, for example if you don't intend to involve that value in an algebraic operation, you can allow the user to leave it null. This is done by checking the Allow Nulls check box for the field.

Here is an example of a table in which the CategoryID and the Picture columns would not Allow Nulls but the the CategoryName and the Description columns would:

To control the nullity of a column with a SQL statement, you can use NULL, NOT NULL, or omit it.

Practical Learning: Controlling Nullity

In the Server Explorer, right-click Students and click Design Table
Under the Allow Nulls column, remove the check boxes the correspond to the StudentNbr and the LastName columns
Save the table

Am I My Record's Keeper?

When updating a record and changing a value, just like the user can make a mistake and change the wrong value, you too can. Consider the following table:

Imagine you ask the user to open this table and, for the video that is rated R, to change the name of the director to Jonathan Lynn. The user would be confused because there is more than one video that is rated R. This means that you should use the most restrictive criterion to locate the record. In future lessons, when we study data analysis, we will review other operators you can use, such as asking the user to locate the video whose title is "The Distinguished Gentleman" AND whose director is Jonathan Lynn.

To be able to uniquely identify each record, you can create a special column and make sure that each value under that column is unique. You have two main options. You can put the responsibility on the user to always provide a unique value. For example, if the table includes records of students of a school, since each student must have a student number and that number must be unique from one student to another, you can ask the user to make sure of this during data entry. What if the user forgets? What if the user cannot get that number at the time of data entry? What if that number can only be generated by the administration but only after the student has been registered? Based on this, an alternative is to ask the SQL interpreter to automatically generate a new and unique number for each record.

A column whose values are automatically generated by the database engine is referred to as an identity column. An identity column can have only a numeric-based data type: bigint, decimal, int, numeric, smallint, or tinyint.

To create an identity column, if you are working the SQL Server Enterprise Manager or the Server Explorer, in the Design Table window, in the top section of the table, create the column by specifying its name and data type as one of the above. Then, in the lower section, set the Identify field to Yes from its default No. Here is an example:

If you are working from a SQL statement, to create an identity column, when creating the table, after the name of the column and before the semi-colon or the closing parenthesis of the last column, enter IDENTITY(),.

After setting the Identity to Yes, you must then specify where the numeric counting would start. By default, this number is set to 1, meaning the first record would have a number of 1, the second would have a number of 2, and so on. If you want, you can ask the interpreter to start with a different number.

To specify the starting value of the identity column, if you are working in the Design Table window, in the lower section of the table, enter the desired value in the Identity Seed field. Here is an example:

If you are working with a SQL statement, to specify the starting value of the identity column, enter the desired number in the parentheses of the IDENTITY keyword.

After the starting value of the identity column has been set, you can specify how much value would be added to the values of the column with each new record. By default, each previous number would be incremented by 1. If you want a different value, you can change it from 1.

To specify the incrementing value of an identity column, if you are working a Design Table window, in the lower portion of the table, enter the desired value in the Identity Increment field. If you are working with a SQL statement, to specify the incrementing value, enter it as the second argument of the IDENTITY keyword. Here is an example:

-- =============================================

-- Database: Sydney University

-- Table:    StaffMembers

-- =============================================

IF EXISTS(SELECT name 

	  FROM 	 sysobjects 

	  WHERE  name = N'StaffMembers' 

	  AND 	 type = 'U')

    DROP TABLE StaffMembers

GO



CREATE TABLE StaffMembers (

StaffNumber int IDENTITY(1,1),

FullName VARCHAR(50) NOT NULL,

Address VARCHAR(80),

City VARCHAR(40),

State VARCHAR(40) NULL DEFAULT = 'NSW',

PostalCode VARCHAR(4) DEFAULT = '2000',

Country VARCHAR(20) DEFAULT = 'Australia')

GO

Practical Learning: Adding an Auto-Incrementing Column

Right-click anywhere in the StudentNbr line and click Insert Column
In the new empty column, set the name to StudentNumber
Set the Data Type to int
In the lower section of the table, double-click the No value of Identifier to change it to Yes.
Notice that the Identity Seed and the Identity Increment have been set to 1
Close the table
When asked whether you want to save, click Yes

The Primary Key

We have seen that an identity column is used to make sure that a table has a certain column that holds a unique value for each record. In some cases, you can use more than one column to uniquely identify each record. For example, on a table that holds the list of employees of a company, you can use both the employee number and the social security number to uniquely identity each record.

In our description of the identity column, we saw that it applied only to one column; but we also mentioned that a more that one column could be used to uniquely identity each record. The column or the combination of columns used to uniquely identity each column is called a primary key.

If you are creating a table in the Design Table window of the SQL Server Enterprise Manager or from the Server Explorer of Microsoft Visual Studio .NET, to indicate the column that would be used as the primary key, first click the name of the column. Then, on the toolbar, click the Primary Key button . You can also right-click the desired column and click Primary Key. The button on the left side of the name of the column would become equipped with a key icon. By tradition, which is not a rule, the name of the column used as the primary key of a table ends with ID. For example, instead of the column being named StaffNumber, it would be named StaffMemberID or something like that.

To specify that more than one column would be used as the primary key, first select them. To do this, you can click the left gray button of one of the column, press Ctrl, and click the left gray button of each of the other columns that would be involved. After selecting the column, on the toolbar, click the Primary button . You can also right-click one of the selected columns and click Primary Key.

Practical Learning: Indicating the Primary Key of a Table

Change the name of the first column from StudentNumber to StudentID
Right-click it and click Primary Key
Save and close the table

CHECK

When performing data entry, in some columns, even after indicating the types of values you expect the user to provide for a certain column, you may want to restrict a range of values that are allowed. This is done using the CHECK constraint.


Previous	Copyright © 2005-2016, FunctionX	Next