CREATE TABLE

A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).

The table owner and the database owner automatically gain the following privileges on the table and are able to grant these privileges to other users:

  • INSERT
  • SELECT
  • TRIGGER
  • UPDATE

These privileges cannot be revoked from the table and database owners.

Only database and schema owners can use the CREATE TABLE statement, which means that table creation privileges cannot be granted to others.

For information about constraints, see  CONSTRAINT clause.

You can specify a default value for a column. A default value is the value to be inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL.

If a qualified table name is specified, the schema name cannot begin with SYS.

The PIN TABLE statements are documented separately in this section.

Syntax

There are two different variants of the CREATE TABLE statement, depending on whether you are specifying the column definitions and constraints, or whether you are modeling the columns after the results of a query expression with the CREATE TABLE AS form:

CREATE TABLE table-Name
  {
      ( {column-definition |
         Table-level constraint}
         [ , {column-definition} ] *
      )
  |
      [ ( column-name ]* ) ]
      AS query-expression [AS <name>]
      WITH NO DATA
  }

table-Name

The name to assign to the new table.

column-definition

A column definition.

The maximum number of columns allowed in a table is 100000.

Table-level constraint

A constraint that applies to the table.

column-name

A column definition.

AS query-expression

See the  CREATE TABLE AS section below.

If this select list contains an expression, you must name the result of the expression. Refer to the final example at the bottom of this topic page.

WITH NO DATA

See the  CREATE TABLE AS section below.

CREATE TABLE … AS …

With this alternate form of the CREATE TABLE statement, the column names and/or the column data types can be specified by providing a query. The columns in the query result are used as a model for creating the columns in the new table.

You cannot include an ORDER BY clause in the query expression you use in the CREATE TABLE AS statement.

If the select list contains an expression, you must name the result of the expression. Refer to the final example at the bottom of this topic page.

If no column names are specified for the new table, then all the columns in the result of the query expression are used to create same-named columns in the new table, of the corresponding data type(s). If one or more column names are specified for the new table, then the same number of columns must be present in the result of the query expression; the data types of those columns are used for the corresponding columns of the new table.

The WITH NO DATA clause specifies that the data rows which result from evaluating the query expression are not used; only the names and data types of the columns in the query result are used.

There is currently a known problem using the CREATE TABLE AS form of the CREATE TABLE statement .when the data to be inserted into the new table results from a RIGHT OUTER JOIN operation. For example, the following statement currently produces a table with all NULL values:

splice> CREATE TABLE t3 AS
   SELECT t1.a,t1.b,t2.c,t2.d
   FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c
   WITH DATA;
0 rows inserted/updated/deleted

There’s a simple workaround for now: create the table without inserting the data, and then insert the data; for example:

splice> CREATE TABLE t3 AS
   SELECT t1.a,t1.b,t2.c,t2.d
   FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c
   WITH NO DATA;
0 rows inserted/updated/deleted

splice> INSERT INTO t3
   SELECT t1.a,t1.b,t2.c,t2.d
   FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c;
0 rows inserted/updated/deleted

Examples

This section presents examples of both forms of the CREATE TABLE statement.

CREATE TABLE

This example creates our Players table:

splice> CREATE TABLE Players(
    ID           SMALLINT NOT NULL PRIMARY KEY,
    Team         VARCHAR(64) NOT NULL,
    Name         VARCHAR(64) NOT NULL,
    Position     CHAR(2),
    DisplayName  VARCHAR(24),
    BirthDate    DATE
    );
0 rows inserted/updated/deleted

This example includes a table-level primary key definition that includes two columns:

splice> CREATE TABLE HOTELAVAILABILITY (
   Hotel_ID INT NOT NULL,
   Booking_Date DATE NOT NULL,
   Rooms_Taken INT DEFAULT 0,
   PRIMARY KEY (Hotel_ID, Booking_Date ));
0 rows inserted/updated/deleted

This example assigns an identity column attribute with an initial value of 5 that increments by 5, and also includes a primary key constraint:

splice> CREATE TABLE PEOPLE (
   Person_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 5, INCREMENT BY 5)
      CONSTRAINT People_PK PRIMARY KEY,
   Person VARCHAR(26) );
0 rows inserted/updated/deleted

For more examples of CREATE TABLE statements using the various constraints, see  CONSTRAINT clause

CREATE TABLE AS

This example creates a new table that uses all of the columns (and their data types) from an existing table, but does not duplicate the data:

splice> CREATE TABLE NewPlayers
   AS SELECT * 
         FROM Players WITH NO DATA;
0 rows inserted/updated/deleted

This example creates a new table that includes the data and uses only some of the columns from an existing table, and assigns new names for the columns:

splice> CREATE TABLE MorePlayers (ID, PlayerName, Born)
   AS SELECT ID, DisplayName, Birthdate
         FROM Players WITH DATA;
94 rows inserted/updated/deleted

This example creates a new table using unnamed expressions in the query and shows that the data types are the same for the corresponding columns in the newly created table:

splice> CREATE TABLE T3 (X,Y)
   AS SELECT 2*I AS COL1, 2.0*F AS COL2
         FROM T1 WITH NO DATA;
0 rows inserted/updated/deleted

See Also