IBM PureData System for Analytics, Version 7.1

CREATE TABLE

Use the CREATE TABLE command to create a new, initially empty table in the current database. The CREATE TABLE command automatically creates a data type that represents the tuple type (structure type) corresponding to one row of the table.

A table cannot have:

The same name as any existing data type.
The same name as a system catalog table.
More than 1600 columns. The effective limit is slightly lower due to tuple-length constraints.
Table or view attributes with the following names:
- cmax
- cmin
- createxid
- ctid
- datasliceid
- deletexid
- oid
- rowid
- tableoid
- xmax
- xmin

The optional constraint clauses specify constraints (or tests) that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is a named rule; that is, an SQL object that helps define valid sets of values by limiting the results of insert, update, or delete operations that are used on a table. Netezza® does not support constraint checks; if you specify constraints, you must conduct the constraint checking and referential integrity.)

You can define table constraints and column constraints.

A column constraint is defined as part of a column definition.
A table constraint definition is not tied to a particular column, and it can encompass more than one column.

You can also write every column constraint as a table constraint. A column constraint is only a notational convenience if the constraint only affects one column.

Distribution specification

Each table in a Netezza RDBMS database has only one distribution key, which consists of one to four columns. You can use the following SQL syntax to create distribution keys.

To create an explicit distribution key, the Netezza SQL syntax is:
```
CREATE TABLE <tablename> [ ( <col>[,<col>… ] ) ] AS 
<select_clause> [ DISTRIBUTE ON [HASH] ( <col>[<col>,… ] ) ]
```
The phrase distribute on specifies the distribution key, the word hash is optional.
To create a round-robin distribution key, the Netezza SQL syntax is:
```
CREATE TABLE <tablename> (col1 int, col2 int, col3 int)
DISTRIBUTE ON RANDOM;
```
The phrase distribute on random specifies round-robin distribution.
To create a table without specifying a distribution key, the Netezza SQL syntax is:
```
CREATE TABLE <tablename> (col1 int, col2 int, col3 int);
```
The Netezza system chooses a distribution key. There is no way to ensure what that key is and it can vary depending on the Netezza software release.

Constraint rule action

You can specify the following actions upon updating or deleting a constraint. Because the system does not enforce constraint checking, these rules are merely accepted rather than used.

CASCADE updates the value of the referencing column to the new value of the referenced column.
SET NULL sets the referencing column to the new value of the referenced column.
SET DEFAULT sets the referenced column
RESTRICT is the same as NO ACTION
NO ACTION produces an error if the foreign key is violated.

Constraint attributes

Constraints can have the following attributes that determine whether the constraint check is immediate or deferred. Because the system does not enforce constraint checking, these attributes are merely accepted rather than used.

[NOT] DEFERRABLE determines whether the constraint is checked at the end of the transaction.
INITIALLY DEFERRED checks the constraint only at the end of the transaction.
INITIALLY IMMEDIATE checks the constraint after each statement.

Syntax

General syntax for the create table command:

CREATE [ TEMPORARY | TEMP ] TABLE <table>
( <col> <type> [<col_constraint>][,<col> <type> [<col_constraint>]…]
[<table_constraint>[,<table_constraint>… ] )
[ DISTRIBUTE ON { RANDOM | [HASH] (<col>[,<col>…]) } ]
[ ORGANIZE ON { (<col>) | NONE } ]
[ ROW SECURITY ]

Where <col_constraint> represents:

[ CONSTRAINT <constraint_name> ] 
{NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT <value> | <ref>}
[ [ [ NOT ] DEFERRABLE ] { INITIALLY DEFERRED | INITIALLY IMMEDIATE } |
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ NOT ] DEFERRABLE ]

Where <table_constraint> represents:

[ CONSTRAINT <constraint_name> ] 
{UNIQUE (<col>[,<col>…] ) |
PRIMARY KEY (<pkcol_name>[,<pkcol_name>…] ) |
FOREIGN KEY (<fkcol_name>[,<fkcol_name>…] ) <ref>}
[ [ [ NOT ] DEFERRABLE ] { INITIALLY DEFERRED | INITIALLY IMMEDIATE } |
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ NOT ] DEFERRABLE ]

Where <ref> represents:

REFERENCES <reftable> [ (<refcol_name>[,<refcol_name>…] ) ]
[ MATCH FULL ]
[ ON UPDATE {CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION} ]
[ ON DELETE {CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION} ]

The system permits and maintains primary key, default, foreign key, unique, and references. The IBM® Netezza system does not support constraint checks and referential integrity. The user must ensure constraint checks and referential integrity.

Inputs

The CREATE TABLE command takes the following inputs:

Table 1. CREATE TABLE inputs
Input	Description
TEMP[ORARY]	The table to be created is to be a temporary table.
<table>	The name of the table to be created.
<col>	The name of a column to be created in the new table.
<type>	The data type of the column.
DISTRIBUTE ON	Each table in the database must have a distribution key that consists of one to four columns. The default is RANDOM, which causes the system to select a random distribution key.
HASH	This parameter can be specified for clarification but has no effect on the command.
ORGANIZE ON	Which columns (from one to four) the table is to be organized on. This cannot be specified for external tables. If columns are specified, the table cannot have any materialized views, and all specified column data types must be zone-mappable. The table data reorganization takes effect when GROOM TABLE is run. For more information, see "Using Clustered Base Tables" in the IBM Netezza System Administrator’s Guide.
ROW SECURITY	Create the table with row-level security.
<constraint_name>	The name that is to be given to a column constraint or table constraint. If you do not specify a name, the system generates one.
NOT DEFERRABLE \| DEFERRABLE	Controls whether the constraint can be deferred to the end of the transaction. NOT DEFERRABLE is the default. (Netezza does not support constraint checking and referential integrity.)
INITIALLY	Specifies either DEFERRED (at the end of the transaction) or IMMEDIATE (at the end of each statement).
NOT NULL \| NULL	Whether the column is allowed to contain null values. NULL is the default.
UNIQUE (column and table constraint)	Whether each value in the column must be unique.
PRIMARY KEY (column and table constraint)	Whether the specified columns are to form the primary key of the table. This constraint is essentially a combination of the UNIQUE and NOT NULL constraints, but identifying a set of columns as a primary key also provides metadata about the design of the schema. A primary key implies that other tables can rely on this set of columns as a unique identifier for rows. You can specify only one primary key constraint for a table, either as a column constraint or as a table constraint. The set of columns that make up the primary key must be different from any other set of columns that is named by any unique constraint defined for the table.
DEFAULT (column constraint)	The default value that is to be placed into each row for this column.
REFERENCES (column constraint)	The specified columns of the new table must only contain values that match values in the specified columns of the specified table.
FOREIGN KEY and REFERENCES table (table constraint)	The specified columns of the new table must only contain values that match values in the specified columns of the specified table. If you do not specify a column, the value must match the primary key of the table. The specified columns of the referenced table must have a unique or primary key constraint in that table.
MATCH FULL	MATCH FULL prevents one column of a multicolumn foreign key from being null if other parts of the foreign key are not null. This is the default. MATCH PARTIAL is unsupported.
ON UPDATE \| ON DELETE	The action that is to be taken when the specified table or columns are updated or deleted: NO ACTION Issues an error if the foreign key is violated. This is the default. RESTRICT Same as NO ACTION. CASCADE Deletes any rows that reference the deleted row. SET NULL Sets the referencing column values to their default value. SET DEFAULT Sets the referencing column values to their default value.

Outputs

The command has the following outputs:

Table 2. CREATE TABLE outputs
Output	Description
`CREATE TABLE`	The system returns this message if the command completes successfully.
`ERROR`	The system returns this message if table creation fails. The error message provides descriptive text, such as: `ERROR: Relation 'table' already exists`.

Privileges

You must be the admin user, the owner of the database or schema, or your account must have the Create Table privilege.

Usage

The following provides sample usage:

To create a table:

MYDB.SCH1(USER)=> 
   CREATE TABLE name (
       code        CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
       title       CHARACTER VARYING(40) NOT NULL,
       did         DECIMAL(3) NOT NULL,
       date_prod   DATE,
       kind        CHAR(10),
       len         INTERVAL HOUR TO MINUTE
   );

To define a primary key table constraint for the table films, you can define primary key table constraints on one or more columns of the table:

MYDB.SCH1(USER)=> 
   CREATE TABLE name (
       code        CHAR(5),
       title       VARCHAR(40),
       did         DECIMAL(3),
       date_prod   DATE,
       kind        VARCHAR(10),
       len         INTERVAL HOUR TO MINUTE,
       CONSTRAINT code_title PRIMARY KEY(code,title)
   );

To define a primary key constraint for the table distributors:

MYDB.SCH1(USER)=> CREATE TABLE distributors (
    did     DECIMAL(3),
    name    CHAR VARYING(40),
    PRIMARY KEY(did)
); 
MYDB.SCH1(USER)=> CREATE TABLE distributors (
    did     DECIMAL(3) PRIMARY KEY,
    name    VARCHAR(40)
);

The two examples are equivalent. The first example uses the table constraint syntax. The second example uses the column constraint notation.

To define two not null column constraints on the table distributors, one of which is explicitly a name:

MYDB.SCH1(USER)=> CREATE TABLE distributors (
       did     DECIMAL(3) CONSTRAINT no_null NOT NULL,
       name    VARCHAR(40) NOT NULL
   );