SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

• Use the LIKE option in the CREATE TABLE statement to create a table like the existing table,
source-table. This approach omits constraints (with the exception of the NOT NULL and
PRIMARY KEY NOT DROPPABLE constraints), headings, and partitions unless
include-option clauses are specified.
Optionally, create the new table with the same constraints and headings as the source table.
Alternately, override these attributes and create the new table with different constraints and
headings.
• When you use the CREATE TABLE statement, for SQL/MX tables, note that it does not provide
the SIMILARITY CHECK clause used with SQL/MP because similarity checking is always on
for NonStop SQL/MX.
For more information, see the SQL/MX Reference Manual.
For more information about CREATE TABLE attributes for SQL/MP that are not supported by NonStop
SQL/MX, see the SQL/MX Comparison Guide for SQL/MP Users.
Managing Table Data
Defining Table 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.
When you define a column, you specify the column name, data type, and, optionally, other column
attributes. To define columns for a table, you can specify the column definitions in the CREATE
TABLE statement or in the ALTER TABLE with the ADD COLUMN clause. However, HP recommends
that you avoid using the ALTER TABLE with the ADD clause if possible insofar as table performance
is degraded by adding columns.
Specifying Column Names
• A column name is an SQL identifier that can be up to 128 characters in length. The column
name must begin with a letter unless it is delimited by double quotes. It must be unique among
column names. It cannot be a reserved word unless it is delimited by double quotes. Regular
SQL identifiers are not case-sensitive but delimited names are. For more information, see the
SQL/MX Reference Manual.
• Use descriptive column names for your application to help programmers and users remember
the names correctly.
• Nondelimited 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
Each column in a table is associated with a data type. These basic formats of data can be stored
in columns:
• Character data
• Numeric data
• Datetime data
• Time interval data
88 Creating an SQL/MX Database