Using the CREATE TABLE Statement to Create Tables
Tables are the primary structures used to hold data in a
relational database. In a typical multi-user environment, the database
administrator (dba) creates the tables that serve as the data stores for the organization's data. Users normally create
their own temporary tables used to store data extracted from the main
organizational tables.
For example, the dba would create the CUSTOMER and ORDERS tables
to hold a permanent record of all of the company's customers and their orders.
If you then need to produce several reports for a particular quarter, you would
create a temporary table to hold a portion of the company's data. By extracting
a portion of the overall table into a temporary table, you have to do the data
selection only once, and you can use the results of the selection for multiple
reports such as quarter-to-quarter comparisons, a list of the top customers
based on amount purchased, or a summary of items sold during the period.
Whether you are creating permanent or temporary tables, you use
the same SQL CREATE TABLE statement. The syntax of the CREATE TABLE statement
is:
CREATE TABLE <table name>
(<column definition> [,...<last column definition]
[<primary key definition>]
[<foreign key definition>])
<column definition> is defined as:
<column name> <data-type> [DEFAULT <value>]
[NOT NULL] [UNIQUE] [<check constraint definition>]
<check constraint definition> is defined as:
CHECK (<search condition>)
<primary key definition> is defined as:
PRIMARY KEY (<column name> [, <column name>])
<foreign key definition> is defined as:
FOREIGN KEY (<column name>) REFERENCES <table name>
Keyword/Option
|
Description
|
|---|---|
table name
|
The name of the table—must be unique by owner within a
database. (See Tip
9, "Understanding Table Names.")
|
column name
|
The name of a column—must be unique within the
table.
|
data-type
|
One of the SQL data types (see Tips 21–26) or a named domain
(see Tip 14,
"Understanding Domains").
|
DEFAULT <value>
|
Value assigned to a column if you create a row and do not
give the column an explicit initial value. (See Tip 51, "Using the DEFAULT Clause in
a CREATE TABLE Statement to Set Default Column Values.")
|
NOT NULL
|
Constraint to prevent the assignment of a NULL value to a
column. (See Tip
191, "Using the NOT NULL Column Constraint to Prevent Null Values in a
Column.")
|
UNIQUE
|
Constraint that prevents adding two table rows with the same
value in the unique column. (See Tip 192, "Using the UNIQUE Column
Constraint to Prevent Duplicate Values in a Column.")
|
CHECK <search condition>
|
The search condition can be any SQL statement that evaluates
to either TRUE or FALSE. The check constraint prevents adding rows to a table
where the search condition evaluates to FALSE. (See Tip 193, "Using the CHECK Constraint
to Validate a Column's Value.")
|
PRIMARY KEY
|
Constraint that prevents adding two table rows with the same
value in the column or set of columns. A table can have only one PRIMARY KEY.
The PRIMARY KEY is column (or set of columns) that can be referenced as a
FOREIGN KEY in another table. (See Tip 172, "Using the PRIMARY KEY Column Constraint to Uniquely
Identify Rows in a Table.")
|
FOREIGN KEY
|
Column whose value can be found as the PRIMARY KEY in table
specified by REFERENCES <table name>. (See Tip 174, "Understanding Referential
Data Integrity Checks and Foreign Keys.")
|
For example, if you execute the CREATE TABLE statements
CREATE TABLE item_master
(item_number INTEGER,
description VARCHAR(35) NOT NULL
PRIMARY KEY (item_number))
CREATE TABLE orders
(order_number INTEGER UNIQUE NOT NULL,
item_number INTEGER NOT NULL,
quantity SMALLINT DEFAULT 1,
item_cost DECIMAL (5, 2),
customer_number INTEGER
PRIMARY KEY (order_number, item_number)
FOREIGN KEY (item_number) REFERENCES item_master)
the DBMS will create two tables, ITEM_MASTER and ORDERS. The
ITEM_MASTER table has two columns, DESCRIPTION and ITEM_NUMBER. The ITEM_NUMBER
is the PRIMARY KEY for the ITEM_MASTER table, meaning that each item (row) in
the table will have a unique ITEM_NUMBER. Put another way, no two item
descriptions will have the same item number.
The second table, ORDERS, has five columns. The PRIMARY KEY of the
ORDERS table is a composite key, meaning that it is made up of two or more
columns. In the current example, the PRIMARY KEY for ORDERS consists of the
ORDER_NUMBER and ITEM_NUM-BER columns, meaning that the same ORDER_NUMBER (like
123, for example) may appear in several rows of the table, but each row for the
same ORDER_NUMBER will have a unique ORDER_NUMBER-ITEM_NUMBER pair.
The FOREIGN KEY constraint in the ORDERS table tells the DBMS that
the value in the ITEM_NUMBER column in the ORDERS table REFERENCES the PRIMARY
KEY in the ITEM_MASTER table. Thus, the DBMS can take the value in the
ITEM_NUMBER column and uniquely identify a single row in the ITEM_MASTER table.
Moreover, the DBMS will not allow you to add a row to the ORDERS table if the
ITEM_NUMBER value in the row to be inserted does not exist in the ITEM_MASTER
table. Conversely, you will not be able to delete a row in the ITEM_MASTER file
if its ITEM_NUMBER value exists as an ITEM_NUMBER in the ORDERS table. (You will
learn more about the FOREIGN KEY constraint )
From its syntax and the examples in this tip, you can see
that the CREATE TABLE statement lets you define database tables. When creating a
table, you give the names of the columns and the data type of each column, and
specify any constraints as to the data values the columns can hold. Moreover,
you can identify one of the columns or a set of columns as the PRIMARY KEY for
the table, meaning that each row in the table has a unique value in the column
(or set of columns) that make up the PRIMARY KEY. Finally, you can use the
FOREIGN KEY constraint to identify parent/child relationships between tables. In
the current example, ORDERS is the child of the ITEM_MASTER
parent.