SAND CDBMS SQL Reference Guide
CREATE TABLE

 

The CREATE TABLE command defines a table in the database. The CREATE TABLE statement must specify the table name, as well as the names and attributes of all columns in the table, or the names of other tables or views whose column structures will be copied in the new table. Other optional table attributes, such as PRIMARY KEY, UNIQUE, FOREIGN KEY, and NOT NULL constraints, can also be included in the definition statement. Alternatively, a SELECT statement can be used to create a new table based on the structure of the query results table, and populate the new table with the values returned by the query.

In MPP mode, the CREATE TABLE command defines a new dimension table, which is replicated across all remote nodes associated with the default partitioned table. If a CREATE TABLE...SELECT statement queries a partitioned table in MPP mode, the resulting table will also be a partitioned table.


Required Privileges

To execute the CREATE TABLE command, the user authorization must own the schema where the table will be created, possess the OWNER privilege on the schema, or possess DBA privileges.

If a domain is referenced in the CREATE TABLE statement, the user authorization must own the domain, own the schema that contains the domain, possess USAGE privileges on the domain, or possess DBA privileges. 

Use of the FOREIGN KEY/REFERENCES clause requires that the user possess one of the following sets of privileges: REFERENCES privileges on the appropriate column(s) of the referenced table or on the schema containing the referenced table, ownership of the referenced table, ownership of or OWNER privileges on the schema containing the referenced table, or DBA privileges on the database.

If the LIKE table clause is used, the user authorization must own the specified table/view, possess OWNER privileges on the schema containing the table/view, or possess DBA privileges.

If a SELECT statement is used, for every base table or view identified in the query expression, the user authorization must own the table/view, own or possess OWNER privileges on the schema containing the table/view, possess SELECT privileges on the table/view, or possess DBA privileges.


Syntax


CREATE TABLE | CREATE LOCAL TABLE
Outside of MPP mode, CREATE TABLE and CREATE LOCAL TABLE function the same way, creating a standard table. In MPP mode, the CREATE TABLE syntax creates a dimension table, which is automatically replicated across all partition nodes. To create a standard table in MPP mode, the CREATE LOCAL TABLE syntax must be used.

table name
The table name argument is an identifier that names the table uniquely within the schema: it cannot match any other table or view name within the current schema. Table names can be up to 128 characters long. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a table name, as this may cause problems when referencing the table in certain SQL statements.

The table may be qualified by the schema to which it will belong: prefix the table name with the name of the appropriate schema followed by a period ( . ), that is, schema-name.table-name.

table name 2
The table name 2 argument is part of the optional LIKE clause, identifying an existing table or view in the database. Each column in this table/view will be copied in the new table, with the same name, definition, and order.

 

Column Definition Clause


column name
The column name argument is an identifier that names the column uniquely within the table. Column names can be up to 128 characters long. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a column name, as this may cause problems when referencing the column in certain SQL statements.

data type
This identifies a valid SAND CDBMS data type for the column. SAND CDBMS data types are described in the Data Types section.

domain name
The domain name argument identifies the domain in which the column values will be stored. The domain must already have been created with the CREATE DOMAIN statement. If the domain is not in the current schema, prefix the domain name with its schema name, separated by a period (schema-name.domain-name).

constant
The optional DEFAULT constant argument specifies a literal value as the default for the column. A numeric value may be preceded by a unary operator (that is, a + or –) to indicate whether it is a positive or negative value. A character string or date/time literal must be surrounded by single quotation marks. The default value must be compatible with the domain/data type of the column.

special constant
The optional DEFAULT special constant argument specifies one of the special system variables supported by SAND:

The special constant must be compatible with the domain/data type of the column.

constraint name
Preceded by the CONSTRAINT keyword, constraint name is an SQL identifier (up to 128 characters long) designating a NOT NULL, UNIQUE, or PRIMARY KEY constraint defined on a single column. A constraint name must be unique within the table. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a constraint name, as this may cause problems when referencing the constraint in certain SQL statements.

The constraint name definition is optional. While SAND CDBMS will generate a default constraint name if one is not specified by the creator of the constraint, it is recommended that each constraint be assigned a meaningful name.

NOT NULL
The NOT NULL constraint prevents null values from being inserted or updated into the designated column.

UNIQUE
The UNIQUE constraint prevents duplicate values from being inserted or updated into the designated column, ensuring that each value in the column is unique among all rows in the named table.

PRIMARY KEY
The PRIMARY KEY constraint ensures that each row of the table can be identified uniquely. The column designated as the primary key for the table is also implicitly UNIQUE and NOT NULL. There can be only one PRIMARY KEY constraint per table.

<column references specification>
The REFERENCES clause (FOREIGN KEY constraint) is used to enforce referential integrity—the relationship between a referencing or dependent table and a referenced or parent table. The referential integrity rule requires that, for any value in the dependent column, there must exist a row in the parent table where the value of the dependent column equals the value of the corresponding column in a UNIQUE or PRIMARY KEY of the parent table.

 

Column References Specification


table name
Identifies the parent table referenced by the FOREIGN KEY constraint.

( column name )
Specifies the column in the parent table on which the FOREIGN KEY is created. If no column name is specified, the FOREIGN KEY constraint references the PRIMARY KEY column of the referenced table.

The data type of the column included in the FOREIGN KEY constraint must exactly match the data type of the corresponding column in the parent (referenced) table. The domains of the respective columns can differ, as long as the domains are defined on exactly the same data type. However, note that using a common domain can improve join performance.

If specified, the referenced column must be enclosed in parentheses.

 

Table Constraint Definition


constraint name
Preceded by the CONSTRAINT keyword, constraint name is an SQL identifier (up to 128 characters long) designating a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint defined on the table. A constraint name must be unique within the table. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a column name, as this may cause problems when referencing the column in certain SQL statements.

The constraint name definition is optional. While SAND CDBMS will generate a default constraint name if one is not specified by the creator of the constraint, it is recommended that each constraint be assigned a meaningful name. This will result in greater intelligibility of error messages issued when constraints are violated, and make it easier to drop constraints (since this must be done by specifying the name of the constraint to be dropped).

column name list
As part of a table constraint definition, the column name list identifies one or more columns on which a UNIQUE, PRIMARY KEY, or FOREIGN KEY is defined. Listed columns are separated by commas, and the entire list must be enclosed in parentheses. A maximum of 64 columns may be included in a key constraint.

In the case of a FOREIGN KEY, the number of columns and their data types must exactly match the number and types of the columns specified in the REFERENCES clause (see below).

UNIQUE
The UNIQUE constraint is used to ensure that the combination of values in the designated column(s) is unique among all the rows in the named table: duplicate value sets are not allowed. A given column name may not appear more than once in the column list of the UNIQUE clause.

PRIMARY KEY
The PRIMARY KEY constraint ensures that each row of the table can be identified uniquely. The columns participating in the primary key cannot already be part of any UNIQUE constraints on the table. A given column name may not appear more than once in the column list of the PRIMARY KEY clause. There can be only one PRIMARY KEY constraint per table.

FOREIGN KEY
The FOREIGN KEY constraint is used to enforce referential integrity—the relationship between a referencing or dependent table and a referenced or parent table. The referential integrity rule requires that, for any value in the dependent column(s), there must exist a row in the parent table where the value of the dependent column(s) equals the value of the corresponding column(s) in a UNIQUE or PRIMARY KEY of the parent table.

 

Table References Specification


table name
Identifies the parent table referenced by the FOREIGN KEY table constraint.

column name list
The column name list identifies one or more columns in the table referenced by the FOREIGN KEY table constraint. Listed columns are separated by commas, and the entire list must be enclosed in parentheses. The referenced columns must comprise either a UNIQUE or PRIMARY KEY constraint. If no column list is specified, the FOREIGN KEY constraint references the PRIMARY KEY column(s) of the referenced table.

 

LIKE Clause

The LIKE clause is used to create a new table with the same definition as an existing table. The columns of the table will have exactly the same names and domains/data types as the source table or view. However, none of the constraints existing on the source table and columns will exist in the new table. Data from the source table is not transferred to the new table.

Multiple LIKE clauses can be included in a CREATE TABLE statement; they may be placed anywhere in the list of column definitions, separated by commas, to reproduce another table or view 's column specifications within the new table.

 

SELECT Clause

The CREATE TABLE...SELECT statement executes a query whose output table provides the structure for the table being defined, and whose return values populate the new table. Each value expression in the SELECT list must have a name or alias defined. If an ORDER BY clause is included, a FETCH FIRST...ONLY clause must be present as well or else the ORDER BY clause will be ignored. Otherwise, any legal query expression can appear as the SELECT clause.

Refer to the description of the SQL SELECT command for further details.

 

Description of CREATE TABLE Actions


The CREATE [LOCAL] TABLE command is used to create base (standard) or dimension tables in the database, depending on the syntax and whether the session is in MPP (Massively Parallel Processing) mode. Outside of MPP mode, CREATE TABLE and CREATE LOCAL TABLE function the same way, creating a standard table. In MPP mode, the CREATE TABLE syntax creates a dimension table that is automatically replicated across all partition nodes, whereas the CREATE LOCAL TABLE syntax creates a standard table.

The order of the column definitions or LIKE clauses determines the left-to-right order of the columns in the created table. The maximum number of columns in a table is 4096.

Table and column constraints (restrictions on the possible data values that can appear in the table) may be included in the CREATE TABLE statement. The table constraints are PRIMARY KEY, FOREIGN KEY, and UNIQUE. The column constraints include the same, along with NOT NULL. Table constraint definitions can be interspersed with the column definitions in any order.

In the case of FOREIGN KEY constraints, there are several referential actions that can be defined to tell the system how to handle dependent records in the referencing table when an attempt is made to delete or update a referenced record in the parent table. The following options are available for both the ON UPDATE and ON DELETE clauses of table and column FOREIGN KEY constraints:

Refer to Other Topics: Constraints for more information about constraints.

If the CREATE TABLE statement contains a query expression, individual column and table constraint definitions cannot also appear in the statement. The new table will have the structure of the query output table, and will be populated with the values returned by the query. The CREATE TABLE...SELECT command may be used to define subsets of tables, or to combine data from several tables into a single table. The projection list of the SELECT statement may contain expressions that are computed from other columns, including aggregate functions such as averages, sums, and so on; however, each of these expressions must be named or have an alias defined.

In some regards, the CREATE TABLE...SELECT command is similar to the CREATE VIEW command. The main conceptual difference is that the result of a CREATE TABLE...SELECT statement is a base table that stores actual values, instead of a logical table that provides indirect access to data. Note as well that a view always reflects the current state of the tables referenced in the defining query, whereas a table produced by CREATE TABLE...SELECT is populated initially with a data set from tables at a particular point in time (that is, when the CREATE TABLE command was executed). Of course, in addition, DML commands cannot be applied to views, and the structure of a view cannot be changed.

In MPP mode, if a CREATE TABLE...SELECT statement queries an existing partitioned table and returns partitioned data, the result will be a new partitioned table. On each remote node associated with the current user's default partitioned table, the result of the SELECT statement on that node will define and populate a new table, which will have the same name and belong to the same schema as the partitioned table being created on the head node. On the head node, a new linked table will be generated automatically (named "<partitioned table>_<connection object>") for each new remote table. The new partitioned table will use (via the associated linked tables) the newly created remote tables as its partitions. The partitioning strategy and key(s), if any, of the partitioned table in the SELECT statement is inherited by the new partitioned table. The SELECT statement in this case is limited to partitioned and dimension tables only; attempting to join a local standard table with a partitioned table, for instance, will produce an error.

The CREATE TABLE...SELECT statement in the MPP context described above cannot be executed in an active transaction. An explicit COMMIT or ROLLBACK may be required to start a new transaction before executing this command.

Note that if the query part of a CREATE TABLE...SELECT statement does not reference a partitioned table in MPP mode, or if the query result set is not partitioned (for example, because of grouping by a non-key field), a new dimension table will be created instead.

Once a table has been created, data can be entered into it using the INSERT command, modified through the UPDATE command, and removed using the DELETE command. The structure of the table can be changed subsequent to its creation by using the ALTER TABLE command to add, drop, or rename columns. Constraints on the table or columns may also be added and dropped through the ALTER TABLE command. A standard table can be converted into a dimension table using the ALTER TABLE...ADD DIMENSION (MPP mode only) or ALTER TABLE...DIMENSION OF command.


Examples

The following example creates a table called inventory:

CREATE TABLE inventory
(   
    item_no SMALLINT NOT NULL,
    description CHAR(12),
    model CHAR(6),
    type CHAR(12),
    weight FLOAT,
    qoh INTEGER,
    unit_cost NUMERIC(8,3),
    price NUMERIC(7,2)
);

The inventory table will have the following structure:
 

Column Name Data Type
item_no SMALLINT (nulls not permitted)
description CHAR(12)
model CHAR(6)
type CHAR(12)
weight FLOAT
qoh INTEGER
unit_cost NUMERIC(8,3)
price NUMERIC(7,2)

 

The following statement defines a new table based partially on the inventory table above:

CREATE TABLE inv_temp
(
     item_code SMALLINT,
     LIKE inventory,
     discount NUMERIC(5,2)
);

The inv_temp table will have this structure:

Column Name Data Type
item_code SMALLINT
item_no SMALLINT
description CHAR(12)
model CHAR(6)
type CHAR(12)
weight FLOAT
qoh INTEGER
unit_cost NUMERIC(8,3)
price NUMERIC(7,2)
discount NUMERIC(5,2)

 

The following table creation statement, executed in MPP mode, defines a dimension table that will be created not only in the local database, but also in each partition node in the network:

CREATE TABLE dimModel
(   
    model_no SMALLINT NOT NULL,
    description CHAR(12),
    model CHAR(6),
    type CHAR(12),
    weight FLOAT
);

Note that if the inventory table was decomposed and redefined as a partitioned (fact) table in relation to the dimModel dimension table above, it could have the following structure:

CREATE PARTITION TABLE p_inventory
(   
    item_no SMALLINT,
    model_no CHAR(6) SMALLINT,
    qoh INTEGER,
    unit_cost NUMERIC(8,3),
    price NUMERIC(7,2)
);

 

Assuming the existence of partitioned table s1.part1 and dimension tables d1 and d2, the following CREATE TABLE...SELECT statement in MPP mode will produce a new partitioned table based on the join of part1, d1, and d2:

CREATE TABLE s1.part3
   SELECT part1.c3 AS sn, d2.c4 AS sname, d1.c4 AS pname, d2.c5 AS qty
   FROM s1.part1, s1.d1, s1.d2
   WHERE part1.c3 = d1.c3 AND d2.c4 = part1.c4;

When the above command is issued, the following internal events will take place in sequence:

  1. The same command is executed on every remote node associated with the user's default partitioned table. For each node, the query will apply to part1's table partition on that node, and dimension tables d1 and d2, which should be identical on all the nodes. The resulting tables will serve as the partitions of the new partitioned table.
  2. On the head node, a linked table is created for each remote table created in Step 1. They will all have the name "part3_connection", where connection is the name of the connection object defined for the remote database.
  3. A new partitioned table is created in schema s1 with the name part3 via a CREATE PARTITION TABLE statement on the head node. The specified partitions are the linked tables created in Step 2. The partitioning strategy of source table part1 (for example, "PARTITION BY HASH (c3)") is included in the definition of the new table (as "PARTITION BY HASH (sn)").

Note that the above command is similar to a CREATE PARTITION TABLE followed by an INSERT...SELECT from partitioned/dimension tables. The CREATE TABLE...SELECT command essentially combines those two commands, although it does not allow the partitioning strategy to change for the new table.

 

As presented, the following other CREATE TABLE commands could be run through interactive or batch SQL:

   CREATE TABLE customer(c_no SMALLINT,
        company CHAR(25), address CHAR(20),
        city CHAR(15), state CHAR(2),
        zip CHAR(5), phone CHAR(14),
        balance DECIMAL (5,2),
        CONSTRAINT prim_key PRIMARY KEY (c_no));

   CREATE TABLE employee (e_no SMALLINT,
        lname CHAR(10), fname CHAR(10),
        street CHAR(20), city CHAR(15),
        st CHAR(2), zip CHAR(5), dept CHAR(4),
        payrate DECIMAL (5,2), com DECIMAL(2,2),
        CONSTRAINT prim_key PRIMARY KEY (e_no));

   CREATE TABLE products (p_no SMALLINT,
        description CHAR(18), price MONEY,
        CONSTRAINT prim_key PRIMARY KEY (p_no));

   CREATE TABLE orders (o_no SMALLINT,
        c_no SMALLINT, o_date DATE, s_no SMALLINT,
        CONSTRAINT prim_key PRIMARY KEY (o_no),
        CONSTRAINT bad_cust FOREIGN KEY (c_no)
        REFERENCES customer ON DELETE RESTRICT,
        CONSTRAINT bad_emp FOREIGN KEY (s_no)
        REFERENCES employee ON DELETE SET NULL);

   CREATE TABLE items (o_no SMALLINT,
        p_no SMALLINT, quantity SMALLINT,
        price DECIMAL (5,2),
        CONSTRAINT bad_ord FOREIGN KEY (o_no)
        REFERENCES orders ON DELETE CASCADE,
        CONSTRAINT bad_prod FOREIGN KEY (p_no)
        REFERENCES products ON DELETE CASCADE);