Creating Tables

In this section: Data Types Null Values Column Names and Aliases CREATE TABLE Example: Creating a New Database Table

A table is a rectangular array of data elements that can be viewed as either a collection of rows or a collection of columns. Every table is managed by a DBMS and accessed using the server.

The columns of a table have an inherent order; its rows do not. Every table must have at least one column but the number of rows can be zero. A table with no rows is said to be empty. If R is the number of rows in a table, and C is the number of columns, each column would have R values, and each row would consist of a sequence of C values arranged in column order.

CREATE is used to bring transient tables or views into existence, INSERT is used to insert a row or a block of rows into a table or view, and DROP is used to explicitly remove transient tables and views from the environment. Since the lifetime of a transient table is limited by the duration of a user session (that period of time during which the application maintains an active connection with the server), DROP is seldom required.

Data Types

Each column of a table has an associated data type. A data type determines the range of values (domain) that can be assigned to the elements of a column and the manner in which the elements can be manipulated and displayed.

Null Values

Columns can be created to contain null values. When a data element is null, we can infer that either a value has not been assigned to it, its value is unknown, or the column is nonapplicable. The existence of null values impacts system behavior in various ways. Comparisons involving null values, for example, are always false (except comparisons using the null predicate). Column functions, like SUM, operating on particular columns, ignore null values. But COUNT(*) counts every row, even if a row contains null values.

Column Names and Aliases

Every column of a table has a name. The name must begin with an alphabetic character (a-z). The rest of the name can contain numbers and the following special characters only:

#
$
@
.
-

CREATE TABLE

CREATE TABLE creates a new database table

CREATE TABLE <table-name> (<column-definition> [,<column- definition>]*)
                  [<default-clause>] [<constraint>]
constraint        ::= NOT NULL [UNIQUE]
default-clause    ::= DEFAULT {<literal> | NULL}
default           ::=  <literal> | NULL
column-definition ::= <column-name> <data-type>
data-type         ::= <string-data-type> | <numeric-data-type>
numeric-data-type ::= NUMERIC[<pr-sc>] | {DECIMAL | DEC} [<pr-sc>] 
       | INTEGER | INT | SMALLINT | FLOAT | REAL | DOUBLE PRECISION
pr-sc             ::=  (<precision>[,<scale>])

where:

table-name

Is the name of the table to be created. The length and format of the table-name must comply with standard SQL requirements.

column-name

Is the name of a column to be created. The length and format of the column-name must comply with standard SQL requirements. The maximum number of columns permitted in one CREATE TABLE is 254.

data-type

Is the data type of the column. Possible values are:

CHAR(n)	Fixed-length alphanumeric, where n is less than 254.
DATE	Date data types are used to store calendar dates.
INTEGER	Four-byte binary integer.
DECIMAL(p,s)	Packed decimal containing p digits with an implied number s of decimal points.
REAL	Four-byte, single-precision floating point.
FLOAT	Eight-byte, double-precision floating point.

The server creates a table when it receives a CREATE TABLE request. It requires that the table name be unique (for example, that it not conflict with the name of an existing table) and that it conform to the lexical constraints imposed on short identifiers (for example, that it consist of the number of characters supported, which typically is eight or less characters).

The number of possible columns in a table is 254. Any server data type can be assigned to a given column.

Example: Creating a New Database Table

CREATE TABLE PERSON
   (
   PID CHAR(8) NOT NULL,
   FIRST_NAME CHAR(20),
   LAST_NAME CHAR(20),
   ADDRESS CHAR(40),
   CITY CHAR(40),
   STATE CHAR(2),
   DOB,
   DATE,
   FLOOR,
   INT,
   SALARY,
   DECIMAL,
   )