About Table Column Attributes

You define table columns to hold your data. When you create a column, you specify the following attributes:

Data Type

The data type attribute defines the kind of data to be stored in the column. When you create a table, you must specify a data type for each of its columns.

Data types define the domain of values that each column can contain. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or SHOE. Each value subsequently inserted in a column assumes the column data type. For example, if you insert 17-JAN-2004 into a date column, then Oracle Database treats that character string as a date value after verifying that it converts to a valid date.

Table 8-1 lists some common Oracle Database built-in data types.

Table 8-1 Common Data Types

Data Type	Description
`VARCHAR2(size` `[BYTE\|CHAR]`)	Variable-length character string having a maximum length of `size` bytes or characters. A column to hold postal codes for different countries, for example, might be restricted to 12 bytes by defining it as `VARCHAR2(12)`. You can use the `CHAR` qualifier, for example `VARCHAR2(10 CHAR)`, to indicate the maximum length in characters, without regard for the number of bytes required. This is especially useful for languages that use characters with double-byte and triple-byte lengths. The `BYTE` and `CHAR` qualifiers override the setting of the `NLS_LENGTH_SEMANTICS` parameter, which has a default of bytes. The maximum size is 4000 bytes or characters. The minimum is 1 byte or 1 character. You must specify `size` for `VARCHAR2`. See Oracle Database Globalization Support Guide for more information.
`NUMBER (p,s)`	Number having precision `p` and scale `s`. Precision sets the maximum number of digits in the number, and scale defines how many of the digits are to the right of the decimal point. For example, a field to hold monetary values might be defined as `NUMBER(12,2)`, providing 10 digits for the primary unit of currency (dollars, pounds, marks, and so on) and two digits for the secondary unit (cents, pennies, pfennigs, and so on). The precision `p` can range from 1 to 38. The scale `s` can range from -84 to 127.
`DATE`	A composite value that includes both a date and time component. For each `DATE` value, the database stores the following information: century, year, month, day, hour, minute, and second. When entering a date into a table column of type `DATE`, you must use the format specified by the `NLS_DATE_FORMAT` initialization parameter. The `NLS_TERRITORY` initialization parameter determines the default value of the `NLS_DATE_FORMAT` parameter. For example, in the United States, the `NLS_DATE_FORMAT` parameter defaults to '`DD-MON-RR`'. You must therefore enter a date in the format '`11-JAN-06`'. Because this format does not include a time component, the time defaults to 12:00:00 a.m. (midnight). You can also use the `TO_DATE` function, which converts a character string to a date, to include a time component or to enter a date in another format. The valid date range is from January 1, 4712 BC to December 31, 9999 AD.
`CLOB`	A character large object (CLOB) containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. The maximum size is (4 gigabytes - 1) * (database block size). For example, for a block size of 32K, the maximum CLOB size is 128 terabytes.

NOT NULL Column Constraint

Constraints determine valid values for the column. In SQL Developer, the only constraint you can define at the column level in the Create Table dialog box page is the NOT NULL constraint, which requires that a value be included in the column whenever a row is inserted or updated. Unlike other constraints described in "About Table-Level Constraints", which can be defined as part of the column definition or part of the table definition, the NOT NULL constraint must be defined as part of the column definition.

Use a NOT NULL constraint when data must be supplied for a column for the integrity of the database. For example, if all employees must belong to a specific department, then the column that contains the department identifier must be defined with a NOT NULL constraint. However, do not define a column as NOT NULL if the data can be unknown or may not exist when rows are added or changed. An example of a column for which you must not use a NOT NULL constraint is the second, optional line in a mailing address.

The database automatically adds a NOT NULL constraint to the column or columns included in the primary key of a table.

Default Value

This value is automatically stored in the column whenever a new row is inserted without a value being provided for the column. You can specify a default value as a literal or as an expression. However, there are limitations on how you construct the expression. See Oracle Database SQL Language Reference for details.

Encryption

You can enable automatic encryption for column data. See the discussion of Transparent Data Encryption in Oracle Database 2 Day + Security Guide for more information.