SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-19
Defining Columns
Defining Columns
To ensure the validity of your database, you must first define columns correctly for the
use of the data and assign data types that provide the best design for your application.
It is the database administrator’s task to consider how the data is used and to assign
appropriate data types and constraints.
To define columns for a table, specify the column definitions in the CREATE TABLE
statement or in ALTER TABLE statements with the ADD COLUMN clause. When you
define a column, you specify the column name, data type, and, optionally, other column
attributes.
For information about constraints, see Creating Constraints on Data on page 5-51.
Specifying Column Names
When naming columns, consider these guidelines:
•
A column name is an SQL identifier that can contain at most 30 of these
characters: letters (A-Z, a-z), digits (0-9), and the underscore (_). The name must
begin with a letter. SQL/MP reserved words, listed in the SQL/MP Reference
Manual, are not allowed as column names.
•
Column names should be descriptive names for your application to help
programmers and users remember the names correctly.
These examples of column definitions show the column names, column
descriptions, and default values:
LOCATION PIC X(20) DEFAULT SYSTEM NOT NULL
AREA CHAR (3) DEFAULT SYSTEM NOT NULL
STATE CHAR (2) DEFAULT "MO" NOT NULL
PHONE PIC 9(7) DEFAULT SYSTEM NOT NULL
LAST_AMOUNT PIC S9(6)V99 COMP DEFAULT SYSTEM NOT NULL
CHANGE_DATE DATETIME YEAR TO MINUTE NOT NULL
EMPLOYEE_NUMBER NUMERIC (4) NO DEFAULT NOT NULL
JOB_CODE_NUMBER SMALLINT DEFAULT SYSTEM NOT NULL
TYPEJOBINCODE NUMERIC (4) UNSIGNED NO DEFAULT
TYPEJOBDESCRIPTION VARCHAR(20) NOT NULL
LAST_VALUE INT UNSIGNED DEFAULT SYSTEM NOT NULL
•
Column names can be specified in the CREATE TABLE statement in any
combination of uppercase and lowercase letters. For example, these three column
names are equivalent: LOCATION, Location, and location.
Specifying Data Types for Columns
Three basic formats of data can be stored in columns:
•
Character and numeric data
•
Binary numeric data
•
Date, time, and time interval data