During data entry, you visitors will encounter web controls that expect
values. Sometimes, for one reason or another, data will
not be available for a particular field. For example, some people have a
middle initial while some others either do not
have it or would not (or cannot) provide it. This aspect can occur
for any field of your table. Therefore, you should think of a way to deal
with it.

To programmatically specify that
a column can
allow null values, type NULL on the right side of the column
definition. To
specify that the values of the column are required, on the right side,
type NOT NULL. If you do not specify NULL or
NOT NULL, the column
will be created as NULL. Here are examples:

If the table was created already and it holds some
values, you cannot change its nullity option.

An Identity Column

One of the goals of a good table is to be able to
uniquely identity each record. To solve this problem,
you can create a particular column whose main purpose is to distinguish
one record from another.

To visually an identity column, in the top section, specify the
name of the column. Then, set its data
type to an integer-based type. Usually, the data type used is int. In the bottom section, click
and expand the Identity Specification property. The first action
you should take is to set its (Is Identity) property from No to Yes.

Once you have set
the value of the (Is Identity) property to Yes, the first time the
user performs data entry, the value of the first record would be set to 1.
This characteristic is controlled by the Identity Seed property. If
you want the count to start to a value other than 1, specify it on this
property.

After the (Is Identity) property has been set to
Yes, the
SQL interpreter would increment the value of each new record by 1, which
is the default. This means that the first record would have a value of 1, the second would have a value of
2, and so on. This aspect is controlled by the Identity Increment
property. If you want to increment by more than that, you can change
the value of the Identity Increment property.

To programmatically create an identity column, type identity followed by parentheses. Between the
parentheses, enter the seed value, followed by a comma, followed by the increment
value. Here is an example:

Sometimes, most records under a certain column may hold the
same value although just a few would be different. To assist the
user with this common value, you create what is referred to as a default value.

You can create a default value of a column when creating a
table. To specify the default value of a column, in the top section, click the
column. In the bottom section, click Default Value or Binding, type the desired
value following the rules of the column's data type.

To specify the default value in a SQL statement, when
creating the column, before the semi-colon or the closing parenthesis of the
last column, type the DEFAULT keyword followed by the value. Here are
examples:

After creating the table, the user does not have to provide a
value for a column that has a default value. If the user does not provide the value,
the default would be used when the record is saved.

Columns and Expressions

There are various ways you can assist the user with
data entry. You can create an expression using
one or a combination of arithmetic and/or SQL operators. You can create an
expression when creating a table.

To visually create an expression when creating a
table, in the top section, specify the column's name (only the column name
is important). In the bottom section, expand the Computed Column
Specification field and, in its (Formula) field, enter the desired
expression.

To programmatically create an expression, in the placeholder of the
column, enter the name of the column, followed by AS, and followed by the
desired expression. Here is an example:

When performing data entry, you must not provide a
value for a column that has an expression; the SQL interpreter would
provide the value automatically. Here is an example of entering data for
the above Circle table:

Some operations are difficult or even impossible to
obtain with an expression, or the operation could become too complex to
achieve. The alternative is to create a function that would take care of
performing the operation and supplying the result to the table.

In order to involve a function with your data entry,
you must have one. You can create your own function. Here is an example:

When performing data entry for this table, you can let
the user enter the customer name and phone number. On the other hand, you
can assist the user by programmatically entering the current date. To do
this, you would call the GETDATE()
function. Here are examples:

You can also involve the function in an operation,
then use the result as the value to assign to a field. You can also call a
function that takes one or more arguments; make sure you respect the rules
of passing an argument to a function when calling it.

If none of the Transact-SQL built-in functions
satisfies your requirements, you can create your own.

Check Constraints

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. A check constraint is a Boolean operation performed
by the SQL interpreter.

To create a check constraint, when creating a table,
right-click anywhere in (even outside) the table and click Check
Constraints...

This would open the Check Constraints dialog box.

To create a check constraint in SQL, first create the
column on which the constraint will apply. Before the closing parenthesis of the table definition, use the
following formula:

CONSTRAINT name CHECK (expression)

The CONSTRAINT and the CHECK keywords are
required. As an object, make sure you provide a name for it. Inside the
parentheses that follow the CHECK operator, enter the expression that will be
applied. Here is an example that will make sure that the hourly salary specified
for an employee is greater than 12.50: