CREATE TABLE

The CREATE TABLE statement defines a table.

Required privilege

CREATE TABLE (if owner) or CREATE ANY TABLE (if not owner).

The owner of the created table must have the REFERENCES privilege on tables referenced by the REFERENCE clause.

SQL syntax

The syntax for a persistent table is:

CREATE TABLE [Owner.]TableName
(
    {{ColumnDefinition} [,...]
     [PRIMARY KEY (ColumnName [,...]) |
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
             [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
     }
)
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[AGING {LRU|
         USE ColumnName
              LIFETIME Num1 {SECOND[S] | MINUTE[S] | HOUR[S] |DAY[S]}
              [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
        }[ON|OFF]
]
[AS SelectQuery]

The syntax for a temporary table is:

CREATE GLOBAL TEMPORARY TABLE [Owner.]TableName
(
    {{ColumnDefinition} [,...]
     [PRIMARY KEY (ColumnName [,...]) |
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
             [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
      }
)
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[ON COMMIT { DELETE | PRESERVE } ROWS ]

Parameters

The CREATE TABLE statement has the parameters:

---

Column Definition

SQL syntax

ColumnName ColumnDataType
 [DEFAULT DefaultVal]
 [[NOT] INLINE]
 [PRIMARY KEY | UNIQUE | 
 NULL [UNIQUE] | 
 NOT NULL [PRIMARY KEY | UNIQUE] ]

Parameters

The column definition has the parameters:

Description

• TimesTen supports one hash index per table. A hash index is defined on the primary key of a table.

• By default, a range index is created to enforce the primary key. Use the UNIQUE HASH clause to specify a hash index for the primary key.

  • If your application performs range queries using a table's primary key, then choose a range index for that table by omitting the UNIQUE HASH clause.

  • If your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause.

• Use the ALTER TABLE statement to change the representation of the primary key index for a table.

• A hash index is created with a fixed number of buckets that remains constant for the life of the table or until the hash index is resized using an ALTER TABLE statement to change hash index size. Fewer buckets in the hash index result in more hash collisions. More buckets reduce collisions but can waste memory. Hash key comparison is a fast operation, so a small number of hash collisions does not cause a performance problem for TimesTen.

  The bucket count is derived as the ratio of the maximum table cardinality, derived from the value of PAGES, to the value 20.To ensure that the hash index is sized correctly, an application must indicate the expected size of the table. This is done with the PAGES parameter. The PAGES parameter should be the expected number of rows in the table, divided by 256. (Since 256 is the number of rows TimesTen stores on each page, the value provided is the expected number of pages in the table.) The application may specify a larger value for PAGES, and therefore fewer rows per bucket on average, if memory use is not an overriding concern.

• At most 16 columns are allowed in a hash key.

• All columns participating in the primary key are NOT NULL.

• A unique hash index can be specified only for the primary key.

• A PRIMARY KEY that is specified in the ColumnDefinition can only be specified for one column.

• PRIMARY KEY cannot be specified in both the ColumnDefinition parameters and CREATE TABLE parameters.

• For both primary key and foreign key constraints, duplicate column names are not allowed in the constraint column list.

• You cannot create a table that has a foreign key referencing a cached table.

• UNIQUE column constraint and default column values are not supported with materialized views.

• If ON DELETE CASCADE is specified on a foreign key constraint for a child table, a user can delete rows from a parent table for which the user has the DELETE privilege without requiring explicit DELETE privilege on the child table.

• To change the ON DELETE CASCADE triggered action, drop then redefine the foreign key constraint.

• ON DELETE CASCADE is supported on detail tables of a materialized view. If you have a materialized view defined over a child table, a deletion from the parent table causes cascaded deletes in the child table. This, in turn, triggers changes in the materialized view.

• The total number of rows reported by the DELETE statement does not include rows deleted from child tables as a result of the ON DELETE CASCADE action.

• For ON DELETE CASCADE: Since different paths may lead from a parent table to a child table, the following rule is enforced:

  • Either all paths from a parent table to a child table are "delete" paths or all paths from a parent table to a child table are "do not delete" paths. Specify ON DELETE CASCADE on all child tables on the "delete" path.

  • This rule does not apply to paths from one parent to different children or from different parents to the same child.

• For ON DELETE CASCADE, a second rule is also enforced:

  • If a table is reached by a "delete" path, then all its children are also reached by a "delete" path.

• For ON DELETE CASCADE with replication, the following restrictions apply:

  • The foreign keys specified with ON DELETE CASCADE must match between the Master and subscriber for replicated tables. Checking is done at runtime. If there is an error, the receiver thread stops working.

  • All tables in the delete cascade tree have to be replicated if any table in the tree is replicated. This restriction is checked when the replication scheme is created or when a foreign key with ON DELETE CASCADE is added to one of the replication tables. If an error is found, the operation is aborted. You may be required to drop the replication scheme first before trying to change the foreign key constraint.

  • You must stop the replication agent before adding or dropping a foreign key on a replicated table.

• The data in a global temporary is private to the current connection and does not need to be secured between users. Thus global temporary tables do not require object privileges.

• After you have defined an aging policy for the table, you cannot change the policy from LRU to time-based or from time-based to LRU. You must first drop aging and then alter the table to add a new aging policy.

• The aging policy must be defined to change the aging state.

• For the time-based aging policy, you cannot add or modify the aging column. This is because you cannot add or modify a NOT NULL column.

• LRU and time-based aging can be combined in one system. If you use only LRU aging, the aging thread wakes up based on the cycle specified for the whole data store. If you use only time-based aging, the aging thread wakes up based on an optimal frequency. This frequency is determined by the values specified in the CYCLE clause for all tables. If you use both LRU and time-based aging, then the thread wakes up based on a combined consideration of both types.

• The following rules determine if a row is accessed or referenced for LRU aging:

  • Any rows used to build the result set of a SELECT statement.

  • Any rows used to build the result set of an INSERT SELECT statement.

  • Any rows that are about to be updated or deleted.

• Compiled commands are marked invalid and need recompilation when you either drop LRU aging from or add LRU aging to tables that are referenced in the commands.

• Call the ttAgingScheduleNow procedure to schedule the aging process immediately regardless of the aging state.

• Aging restrictions:

  • LRU aging and time-based aging are not supported on detail tables of materialized views.

  • LRU aging and time-based aging are not supported on global temporary tables.

  • You cannot drop the column that is used for time-based aging.

  • The aging policy and aging state must be the same in all sites of replication.

  • Tables that are related by foreign keys must have the same aging policy.

  • For LRU aging, if a child row is not a candidate for aging, neither this child row nor its parent row are deleted. ON DELETE CASCADE settings are ignored.

  • For time-based aging, if a parent row is a candidate for aging, then all child rows are deleted. ON DELETE CASCADE (whether specified or not) is ignored.

• If you specify the AS SelectQuery clause:

  • Data types and data type lengths are derived from the SelectQuery. Do not specify data types on the columns of the table you are creating.

  • TimesTen defines on columns in the new table NOT NULL constraints that were explicitly created on the corresponding columns of the selected table if SelectQuery selects the column rather than an expression containing the column.

  • NOT NULL constraints that were implicitly created by TimesTen on columns of the selected table (for example, primary keys) are carried over to the new table. You can override the NOT NULL constraint on the selected table by defining the new column as NULL. For example: CREATE TABLE newtable (newcol NULL) AS SELECT (col) FROM tab;

  • NOT INLINE/INLINE attributes are carried over to the new table.

  • Unique keys, foreign keys, indexes and column default values are not carried over to the new table.

  • If all expressions in SelectQuery are columns, rather than expressions, then you can omit the columns from the table you are creating. In this case, the name of the columns are the same as the columns in SelectQuery. If the SelectQuery contains an expression rather than a simple column reference, either specify a column alias or name the column in the CREATE TABLE statement.

  • Do not specify foreign keys on the table you are creating.

  • Do not specify the SELECT FOR UPDATE clause in SelectQuery.

  • SelectQuery cannot contain set operators UNION, MINUS, INTERSECT.

  • Global temporary tables are not supported.

Examples

A range index is created on partnumber because it is the primary key.

Command> CREATE TABLE price
> (partnumber INTEGER NOT NULL PRIMARY KEY,
> vendornumber INTEGER NOT NULL,
> vendpartnum CHAR(20) NOT NULL,
> unitprice DECIMAL(10,2),
> deliverydays SMALLINT,
> discountqty SMALLINT);
Command> INDEXES price;
Indexes on table SAMPLEUSER.PRICE:
 PRICE: unique range index on columns:
   PARTNUMBER
1 index found.
1 table found.

A hash index is created on column clubname, the primary key.

CREATE TABLE recreation.clubs
(clubname CHAR(15) NOT NULL PRIMARY KEY,
 clubphone SMALLINT,
 activity CHAR(18))
UNIQUE HASH ON (clubname) PAGES = 30;

A range index is created on the two columns membername and club because together they form the primary key.

Command> CREATE TABLE recreation.members
> (membername CHAR(20) NOT NULL,
>  club CHAR(15) NOT NULL,
>  memberphone SMALLINT,
>  PRIMARY KEY (membername, club));
Command> INDEXES recreation.members;
Indexes on table RECREATION.MEMBERS:
  MEMBERS: unique range index on columns:
    MEMBERNAME
    CLUB
  1 index found.
1 table found.

No hash index is created on the table recreation.events.

CREATE TABLE recreation.events
(sponsorclub CHAR(15),
 event CHAR(30),
 coordinator CHAR(20),
 results VARBINARY(10000));

A hash index is created on the column vendornumber.

CREATE TABLE purchasing.vendors
(vendornumber INTEGER NOT NULL PRIMARY KEY,
 vendorname CHAR(30) NOT NULL,
 contactname CHAR(30),
 phonenumber CHAR(15),
 vendorstreet CHAR(30) NOT NULL,
 vendorcity CHAR(20) NOT NULL,
 vendorstate CHAR(2) NOT NULL,
 vendorzipcode CHAR(10) NOT NULL,
 vendorremarks VARCHAR(60))
UNIQUE HASH ON (vendornumber) PAGES = 101;

A hash index is created on the columns membername and club because together they form the primary key.

CREATE TABLE recreation.members
    (membername CHAR(20) NOT NULL,
        club CHAR(15) NOT NULL,
        memberphone SMALLINT,
        PRIMARY KEY (membername, club))
    UNIQUE HASH ON (membername, club) PAGES = 100;

A hash index is created on the columns firstname and lastname because together they form the primary key in the table authors. A foreign key is created on the columns authorfirstname and authorlastname in the table books that references the primary key in the table authors.

CREATE TABLE authors
    (firstname VARCHAR(255) NOT NULL,
        lastname VARCHAR(255) NOT NULL,
        description VARCHAR(2000),
        PRIMARY KEY (firstname, lastname))
    UNIQUE HASH ON (firstname, lastname) PAGES=20;
CREATE TABLE books
    (title VARCHAR(100),
    authorfirstname VARCHAR(255),
    authorlastname VARCHAR(255),
    price DECIMAL(5,2),
    FOREIGN KEY (authorfirstname, authorlastname) 
    REFERENCES authors(firstname, lastname));

The following statement overrides the default character of VARCHAR columns and creates a table where one VARCHAR (10) column is NOT INLINE and one VARCHAR (144) is INLINE:

CREATE TABLE t1
    (c1 VARCHAR(10) NOT INLINE NOT NULL,
    c2 VARCHAR(144) INLINE NOT NULL);

The following statement creates a table with a UNIQUE column for book titles:

CREATE TABLE books
    (title VARCHAR(100) UNIQUE,
        authorfirstname VARCHAR(255),
        authorlastname VARCHAR(255),
        price DECIMAL(5,2),
        FOREIGN KEY (authorfirstname, authorlastname)
        REFERENCES authors(firstname, lastname));

The following statement creates a table with a default value of 1 on column x1 and a default value of SYSDATE on column d:

CREATE TABLE t1
    (x1 INT DEFAULT 1, d TIMESTAMP DEFAULT SYSDATE);

This example creates the rangex table and defines col1 as the primary key. A range index is created by default.

Command> CREATE TABLE rangex (col1 TT_INTEGER PRIMARY KEY);
Command> INDEXES rangex;
Indexes on table SAMPLEUSER.RANGEX:
  RANGEX: unique range index on columns:
    COL1
  1 index found.
1 table found.

The following statement illustrates the use of the ON DELETE CASCADE clause for parent/child tables of the HR schema. Tables with foreign keys have been altered to enable ON DELETE CASCADE.

ALTER TABLE countries
ADD CONSTRAINT countr_reg_fk
         FOREIGN KEY (region_id)
           REFERENCES regions(region_id) ON DELETE CASCADE;
ALTER TABLE locations
     ADD CONSTRAINT loc_c_id_fk
          FOREIGN KEY (country_id)
                    REFERENCES countries(country_id) ON DELETE CASCADE;
ALTER TABLE departments
     ADD CONSTRAINT dept_loc_fk
         FOREIGN KEY (location_id)
           REFERENCES locations (location_id) ON DELETE CASCADE;
ALTER TABLE employees
     ADD CONSTRAINT     emp_dept_fk
         FOREIGN KEY (department_id)
           REFERENCES departments ON DELETE CASCADE;
ALTER TABLE employees
     ADD CONSTRAINT     emp_job_fk
         FOREIGN KEY (job_id)
           REFERENCES jobs (job_id);
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_job_fk
         FOREIGN KEY (job_id)
           REFERENCES jobs;
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_emp_fk
         FOREIGN KEY (employee_id)
           REFERENCES employees ON DELETE CASCADE;
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_dept_fk
         FOREIGN KEY (department_id)
           REFERENCES departments ON DELETE CASCADE;
     ;

This example shows how time resolution works with aging.

If lifetime is 3 days (resolution is in days):

• If (SYSDATE - ColumnValue) <= 3, do not age.

• If (SYSDATE - ColumnValue) > 3, then the row is a candidate for aging.

• If (SYSDATE - ColumnValue) = 3 days, 22 hours. The row is not aged out if you specified a lifetime of 3 days. The row would be aged out if you had specified a lifetime of 72 hours.

This example creates a table with LRU aging. Aging state is ON by default.

CREATE TABLE agingdemo
       (agingid NUMBER NOT NULL PRIMARY KEY
        ,name  VARCHAR2 (20)
       )
       AGING LRU;
Command> DESCRIBE agingdemo;
Table USER.AGINGDEMO:
  Columns:
     *AGINGID NUMBER NOT NULL
     NAME VARCHAR2 (20) INLINE
     AGING LRU ON
1 table found.
(primary key columns are indicated with *)

This example creates a table with time-based aging. Lifetime is 3 days. Cycle is not specified, so the default is 5 minutes. Aging state is OFF.

CREATE TABLE agingdemo2
       (agingid NUMBER NOT NULL PRIMARY KEY
        ,name  VARCHAR2 (20)
        ,agingcolumn TIMESTAMP NOT NULL
        )
        AGING USE agingcolumn LIFETIME 3 DAYS OFF;
Command> DESCRIBE agingdemo2;
Table USER.AGINGDEMO2:
  Columns:
     *AGINGID NUMBER NOT NULL
     NAME VARCHAR2 (20) INLINE
     AGINGCOLUMN TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 3 days cycle 5 minutes off
1 table found.
(primary key columns are indicated with *)

This example generates an error message. It illustrates that after you create an aging policy, you cannot change it. You must drop aging and redefine aging.

CREATE TABLE agingdemo2
        (agingid NUMBER NOT NULL PRIMARY KEY
        ,name  VARCHAR2 (20)
        ,agingcolumn TIMESTAMP NOT NULL
        )
        AGING USE agingcolumn LIFETIME 3 DAYS OFF;
ALTER TABLE agingdemo2
        ADD AGING LRU;
 2980: Cannot add aging policy to a table with an existing aging policy. Have to drop the old aging first
The command failed.
DROP aging on the table and redefine with LRU aging.
ALTER TABLE agingdemo2
        DROP AGING;
ALTER TABLE agingdemo2
        ADD AGING LRU;
Command> DESCRIBE agingdemo2;
Table USER.AGINGDEMO2:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging lru on
1 table found.
(primary key columns are indicated with *)

Attempt to create a table with time-based aging. Define aging column with data type TT_DATE and LIFETIME 3 hours. An error is generated because the LIFETIME unit must be expressed as DAYS.

Command> CREATE TABLE aging1 (col1 TT_INTEGER PRIMARY KEY, 
         col2 TT_DATE NOT NULL) AGING USE col2 LIFETIME 3 HOURS;
 2977: Only DAY lifetime unit is allowed with a TT_DATE column
The command failed.

Use AS SelectQuery clause to create the table emp. Select last_name from the employees table where employee_id between 100 and 105. You see 6 rows inserted into emp. First issue the SELECT statement to see rows that should be returned.

Command> SELECT last_name FROM employees WHERE employee_id BETWEEN 100 AND 105;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
< Austin >
6 rows found.
Command> CREATE TABLE emp AS SELECT employee_id FROM employees
>WHERE employee_id BETWEEN 100 AND 105;
6 rows inserted.
Command> SELECT * FROM emp;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
< Austin >
6 rows found.

Use AS SelectQuery to create table totalsal. Sum salary and insert result into totalsalary. Define alias s for SelectQuery expression.

Command> CREATE TABLE totalsal AS SELECT SUM (salary) s FROM employees;
1 row inserted.
Command> SELECT * FROM totalsal;
< 691400 >
1 row found.

Use AS SelectQuery to create table defined with column commission_pct. Set default to .3. First describe table employees to show that column commission_pct is of type NUMBER (2,2). For table c_pct, column commission_pct inherits type NUMBER (2,2) from column commission_pct of employees table.

Command> DESCRIBE employees;
Table SAMPLEUSER.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE UNIQUE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)

1 table found.
(primary key columns are indicated with *)
Command> CREATE TABLE c_pct (commission_pct DEFAULT .3) AS SELECT
         commission_pct FROM employees;
107 rows inserted.
Command> DESCRIBE c_pct;

Table SAMPLEUSER.C_PCT:
  Columns:
    COMMISSION_PCT                 NUMBER (2,2) DEFAULT .3

1 table found.
(primary key columns are indicated with *)

See also

ALTER TABLE
DROP TABLE
TRUNCATE TABLE
UPDATE