AntDB-Oracle Compatibility Developer's Manual P3–12
Published: 2023-09-01 05:32:18

#### CREATE TABLE

**Name**

CREATE TABLE - define a new table

**Summary**

CREATE \[ GLOBAL TEMPORARY ] TABLE table\_name (

{ column\_name data\_type \[ DEFAULT default\_expr ]

\[ column\_constraint \[ ... ] ] \| table\_constraint } \[, ...]

)

\[ ON COMMIT { PRESERVE ROWS \| DELETE ROWS } ]

\[ TABLESPACE tablespace ]

\[ table\_partitioning\_clauses ]

\[WITH NOLOGGING]

Column\_constraint is one of the foloowing options:

\[ CONSTRAINT constraint\_name ]

{ NOT NULL \|

NULL \|

UNIQUE \[ USING INDEX TABLESPACE tablespace ] \|

PRIMARY KEY \[ USING INDEX TABLESPACE tablespace ] \|

CHECK (expression) \|

REFERENCES reftable \[ ( refcolumn ) ]

\[ ON DELETE action ] }

\[ DEFERRABLE \| NOT DEFERRABLE ]

Table\_constraint is one of the foloowing options:

\[ CONSTRAINT constraint\_name ]

{ UNIQUE ( column\_name \[, ...] )

\[ USING INDEX TABLESPACE tablespace ] \|

PRIMARY KEY ( column\_name \[, ...] )

\[ USING INDEX TABLESPACE tablespace ] \|

CHECK ( expression ) \|

FOREIGN KEY ( column\_name \[, ...] )

REFERENCES reftable \[ ( refcolumn \[, ...] ) ]

\[ ON DELETE action ] }

\[ DEFERRABLE \| NOT DEFERRABLE ]

The table\_partitioning\_clauses is:

PARTITION BY RANGE ( column\_name \[, ... ] )

(

PARTITION partition VALUES LESS THAN {( expr \[, ... ] )\| MAXVALUE }

\[ TABLESPACE tablespace ]

\[, ... ]

)

Or 

PARTITION BY LIST ( column\_name \[, ... ] )

(

PARTITION partition VALUES(expre \[, ... ])

\[ TABLESPACE tablespace ] \[, ... ])

)

Or

PARTITION BY HASH ( column\_name \[, ... ] )

(

PARTITION partition VALUES(expre \[, ... ])

\[ TABLESPACE tablespace ] \[, ... ])

)

**Description**

The CREATE TABLE command creates a new table in the current database. The newly created table has no content in its initial state. The user who executes the CREATE TABLE command is the owner of the newly created table.

If a schema name is specified (for example, CREATE TABLE myschema.mytable ...) ...), then the table is created in the specified schema, otherwise it is created in the current schema. Temporary tables only exist in a specific schema, so you do not need to specify a schema name when creating a temporary table. The table name cannot be the same as the name of other tables, sequences, indexes or views in the same schema.

The CREATE TABLE command also automatically creates a data type to represent the composite type corresponding to a row in the table. Therefore, a table cannot have the same name as an existing data type in the same schema.

A table can have up to 1600 data columns (and in practice, the true effective limit is somewhat lower due to field length limitations).

The optional constraint clause specifies the constraint (or test) conditions. Only if these constraint (or test) conditions are met can the insert or update operation of a row be performed. A constraint is actually a SQL object that helps define a collection of valid values in a table in various ways.

There are two ways to define constraints here: table-level constraints and column-level constraints. Column-level constraints are defined as part of the column definition. A table-level constraint is not associated with a specific column; it includes multiple columns. We can write each column-level constraint as a table-level constraint: if the constraint affects only one column, then a column-level constraint is a simple representation.

**argument**

GLOBAL TEMPORARY

If this parameter is specified, then a temporary table will be created. The temporary table is automatically deleted at the end of the session, or we can do this at the end of the current transaction (see the parameter ON COMMIT below for details). If a permanent table with the same name exists, then while the temporary table exists, the permanent table is not visible to the current session, unless it is referenced by a schema-qualified name. In addition to this, a temporary table is also invisible outside the scope of the session in which it was created (global temporary tables are incompatible with Oracle in this respect). Any indexes created on a temporary table also exist temporarily.

table\_name

The name of the created table (which can be referenced in a schema-qualified manner).

column\_name

Create the name of the column in new table.

data\_type

The data type of column. Data types may include array definitions.

DEFAULT default\_expr

The DEFAULT clause assigns a default value to the column. This value can be any expression (but subqueries and references to other columns in the current table are not allowed), and the data type of the default expression must match the data type of the column. We can use the default expression when no value is specified for the column in the insert operation. If no default value is specified for the column. Then the default value is null.

CONSTRAINT constraint\_name

The name specified for the column or table constraint (this name is optional). If not specified, then a name is automatically generated for the constraint.

NOT NULL

Do not allow columns to contain null values.

NULL

Allow columns to contain null values. This is the default case. This clause is only provided for compatibility with non-standard SQL databases. This parameter is not advocated in new applications.

UNIQUE - Column-level constraints

UNIQUE (column\_name \[, ...] ) - Table Level Constraint

A UNIQUE constraint specifies that a set of columns in a table can contain only unique values. A uniqueness table-level constraint operates the same as a column-level constraint, except that it has the ability to constrain on multiple columns.

For a uniqueness constraint, it does not consider null values to be equal.

The set of columns named by each unique table-level constraint cannot be the same as the set of columns named by other unique or primary key constraints defined on the table. (Otherwise two identical constraints would be listed)

PRIMARY KEY – column-level constraint

PRIMARY KEY ( column\_name \[, ...] ) – table-level constraint

A primary key constraint specifies that one or more columns of data on a table contain only unique (non-repeatable), non-null values. Technically, a primary key is simply a combination of a uniqueness constraint and a non-null constraint. But identifying a collection of columns as a primary key provides metadata about the schema design, because the primary key causes other tables to use the collection of columns as a unique identifier for the record.

Only one primary key can be specified per table, whether as a column-level constraint or a table-level constraint.

The set of columns named for a primary key constraint should be different from the set of columns named for other uniqueness constraints on the same table.

CHECK (expression)

The CHECK clause specifies an expression that produces a Boolean result value. An insert or update operation will succeed only if this Boolean expression is satisfied. If the result of the expression is "true" or "unkonw", then the condition is satisfied. Any insert or update operation that fails to satisfy the expression will generate an error exception, and the insert or update operation cannot change the value of the database record. Check constraints specified as column-level constraints can only access the values of the relevant columns, while expressions appearing in table-level constraints can refer to multiple column values.

Currently, CHECK expressions cannot contain subqueries or variables that refer to columns other than the current record.

REFERENCES reftable \[ ( refcolumn ) ] \[ ON DELETE action ] - column-level constraint

FOREIGN KEY ( column \[, ...] ) REFERENCES reftable \[ ( refcolumn \[, ...] ) ] \[ ON DELETE action ] - column-level constraint

These clauses specify a foreign key constraint that requires the relevant columns of a newly created table to contain only values that match the columns in the referenced table. If refcolumn is not used, then the primary key of the reftable is used. The referenced column must be a column with a unique or primary key constraint in the referenced table.

In addition, when the data of the referenced column changes, the relevant operation is performed on the relevant column in this table. The ON DELETE clause specifies the operation to be performed when deleting a row from the referenced table. While constraints can be deferred, referencing operations cannot be deferred. For each clause, here are some of the operations that can be allowed.

CASCADE

Delete any records that reference the deleted record, or update the value on the referenced column to the value of the referenced column, respectively.

SET NULL

Set the value of the referenced column to null. If the value of the referenced column changes frequently, it is a good idea to add an index to the foreign key column. This way the referencing operations associated with the foreign key column can be performed more efficiently.

DEFERRABLE

NOT DEFERRABLE

Used to control whether a constraint can be delayed or not. Non-delayable constraints are checked immediately after each command is executed. Delayable constraint checks are performed only after the transaction ends. NO DEFERRABLE is the default option. Only foreign key constraints currently use this clause. All other constraint types are non-delayable.

ON COMMIT

The processing of temporary tables at the end of a transaction block can be controlled by ON COMMIT. There are two options here.

PRESERVE ROWS

No specific operation is performed at the end of a transaction. This is the default system behavior (note that it is not compatible with Oracle. Oracle is DELETE ROWS by default).

DELETE ROWS

Deletes all rows in the temporary table at the end of each transaction. In essence, a TRUNCATE operation is automatically performed after each commit.

TABLESPACE tablespace

tablespace is the name of the tablespace where the newly created table is located. If it is not specified, it is the tablespace specified with the parameter default\_tablespace. If the value of the parameter default\_tablesapce is empty, then the default tablespace of the database is used.

USING INDEX TABLESPACE tablespace

This clause allows to select the tablespace where the indexes related to uniqueness or primary keys are created. If it is not specified, it is the tablespace specified with the parameter default\_tablespace. If the value of the default\_tablesapce parameter is empty, then the database's default tablespace is used.

PARTITION BY RANGE ( column\_name \[, ... ] )

The partition clause is used to create partitioned tables. The PARTITION BY RANGE keyword is used to create range partitions. It shows the values in a range using a set of VALUES LESS THAN operators. When using range partitioning, you must define at least one partition using VALUES LESS THAN.

PARTITION BY LIST ( column\_name \[, ... ] )

Create list partitions

PARTITION BY HASH ( column\_name \[, ... ] )

Create hash partitions

WITH NOLOGGING

Refers to unlogged wal log, fast write speed, no data in the backup database, only the structure. When the database crashes, the data of unlogged table is automatically cleared when the database restarts. When the database is shut down normally and started again, the unlogged table has data.

**Note**

To enforce uniqueness, AntDB automatically creates an index for each uniqueness constraint or primary key constraint. So for columns that identify the primary key, there is no need to explicitly create an index. (See CREATE INDEX command for more information)

**Example**

Create tables dept and emp.

```
CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14),
loc	VARCHAR2(13)
);
```

Create a foreign key constraint. Note that the standalone version supports the following SQL, but in the AntDB Cluster version, the foreign key and primary key must be on a single field.

```
CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
```

Define a unique table-level constraint for the dept table. The standalone version can define a unique table-level constraint on multiple columns in the table. But the clustered version can only create a uniqueness constraint on one column.

```
CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
loc	VARCHAR2(13)
);
```

Define a column-level check constraint.

```
CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
```

Define a table-level check constraint.

```
CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno),
CONSTRAINT new_emp_ck CHECK (ename IS NOT NULL AND empno > 7000)
);
```

Define a primary key table-level constraint for the table jobhist. We can define primary key table-level constraints on multiple columns in a table. Note that in the clustered version, the primary key column must be a slice key.

```
CREATE TABLE jobhist (
empno	NUMBER(4) NOT NULL,
startdate	DATE NOT NULL,
enddate	DATE,
job	VARCHAR2(9),
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2),
chgdesc	VARCHAR2(80),
CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate));
```

Assign a text constant default value to the column job and set the date the record was inserted to the default value of the column hiredate.

```
CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9) DEFAULT 'SALESMAN',
mgr	NUMBER(4),
hiredate	DATE DEFAULT SYSDATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
```

Create table dept in tablespace diskvol1.

```
CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14),
loc	VARCHAR2(13)
) TABLESPACE diskvol1;
```

Create the range partitioned table.

```
CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE custs01 
);
```

Create LIST partitioned table.Create hash partitions.

```
CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY LIST (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES(100000,200000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES(300000) TABLESPACE custs01 
);
```