Field attributes (null value, default value, marked field/auto-increment field)

Field attribute
The previous section describes how to create a table containing different types of fields. In this section, you will learn how to use the three attributes of a field. These attributes allow you to control null values, default values, and identity values.

Allow and disable null values
Most fields can accept null values ). When a field accepts a null value, it remains null if you do not change it. Null is different from zero. Strictly speaking, null indicates that no value exists.
To allow a field to accept null values, you must use the expression null after the field definition. For example, the following table allows null values for both fields:
Create Table empty (empty1 char (40) null, empty2 int null (
Note:
BIT data cannot be null. A field of this type must be 0 or 1.
Sometimes you need to disable a field from using NULL values. For example, if a table contains a credit card number and a valid date, you do not want someone to enter a credit card number but do not enter a valid date. To force both fields to input data, you can use the following method to create the table:
Create Table creditcards (creditcard_number char (20) not null,
Creditcard_expire datetime not null)
Note that the field definition is followed by the expression not null. By using the include expression not null, You can prohibit anyone from inserting data in only one field without entering data in another field.
You will find that this capability of prohibiting null values is very useful when you build your own outlets. If you specify a field that cannot accept null values, an error warning is triggered when you try to enter a null value. These error warnings can provide valuable clues for program debugging.

Default Value
Suppose there is a table that stores address information. The fields in this table include street, city, state, zip code, and country. If you expect most of the addresses to be in the United States, you can use this value as the default value of the country field.
To specify the default value when creating a table, you can use the default expression. See the following example of using the default value when creating a table:
Create Table addresses (street varchar (60) null,
City varchar (40) null,
State varchar (20) null
Zip varchar (20) null,
Country varchar (30) default 'usa ')
In this example, the default value of the country field is specified as the United States. Note the use of single quotes. The quotation marks indicate that this is character-type data. To specify a default value for a non-linear field, do not extend the value in quotation marks:
Create Table orders (price money default $38.00,
Quantity int default 50,
Entrydate datetime default getdate ())
In this create table statement, a default value is specified for each field. Note the default value specified by the datetime field entrydate. The default value is the return value of the function getdate (). This function returns the current date and time.

Id field
Each table can have one or only one identification field. An ID field is a special field that uniquely identifies each record in a table. For example, the table jobs in the database pubs contains a unique identifier for each work identity field:
Job_id job_desc
......................................................................
1 New hire job not specified
2 Chief Executive Officer
3 bushness Operations Manager
4. Chief Financial officier
5 Publisher
The job_id field provides a unique number for each job. If you decide to add a new job, the job_id field of the new record will be automatically assigned to a new unique value.
To create an identity field, you only need to add the expression identity after the field definition. You can only set numeric or Int fields as the ID field. Here is an example:
Create Table visitorid (theid nuberic (18) identity, name varchar (40 ))
The table created by this statement contains an Identifier Field named theid. When a new visitor name is added to this table, this field is automatically assigned to a new value. You can use this table to provide a unique identifier for each user of your site.
TIPS:
When creating a field, be sure to use a data type that is large enough. For example, if you use tinyint data, you can only add 255 records to the table. If you expect a table to become very large, you should use numeric data.
The existence of the field will make you want to try many impossible things. For example, you may want to use the identification field to perform operations on records based on their locations in the table. You should discard this intention. The values of each record's identity field are different. However, this does not prohibit the interval between the numbers of an identity field. For example, you should never try to use the identification field of a table to retrieve the first 10 records in the table. This operation will cause failure. For example, record 6 and record 7 do not exist at all.