Create a table

Use the CREATE TABLE statement to create a table.

This topic describes how to create a non-partitioned table. For information about how to create a partitioned table, see Create a partitioned table.

Create a non-partitioned table

A non-partitioned table is a table with only one partition.

Here is a sample statement for creating a non-partitioned table:

obclient>CREATE TABLE table_name1(w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);

Query OK, 0 rows affected

obclient>CREATE TABLE table_name2 (c_w_id int NOT NULL
, c_d_id int NOT null
, c_id int NOT null
, c_discount decimal(4, 4)
, c_credit char(2)
, c_last varchar(16)
, c_first varchar(16)
, c_middle char(2)
, c_balance decimal(12, 2)
, c_ytd_payment decimal(12, 2)
, c_payment_cnt int
, c_credit_lim decimal(12, 2)
, c_street_1 varchar(20)
, c_street_2 varchar(20)
, c_city varchar(20)
, c_state char(2)
, c_zip char(9)
, c_phone char(16)
, c_since date
, c_delivery_cnt int
, c_data varchar(500)
, index icust(c_last, c_d_id, c_w_id, c_first, c_id)
, FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
, primary key (c_w_id, c_d_id, c_id)
);

Query OK, 0 rows affected

This example creates two tables and defines some constraints on the columns of the tables, including primary keys and foreign keys created on different columns. For information about constraints, see Define column constraints.

When creating columns for a table, choose the correct data types. For information about data types, see Overview.

Note

• To ensure system performance and facilitate maintenance, it is recommended that you specify a primary key or a unique key when creating a table. If no field can be used as the primary key, you can create the table without specifying a primary key. After the table is successfully created, the system will assign an auto-incrementing column as a hidden primary key for tables without a primary key. For information about sequences, see Manage sequences.

• Since adding a table's primary key is not supported through ALTER TABLE, you must set the primary key when creating the table.

Create a table by copying data from an existing table

Copy table data

Use the CREATE TABLE AS SELECT statement to copy data from a table, but the table schema will not be exactly the same. Information such as indexes, constraints, default values, and partitions are lost during the copy operation.

Here is a sample statement:

obclient>CREATE TABLE t1_copy AS SELECT * FROM t1;
Query OK, 3 rows affected

Copy table schema

You can use the CREATE TABLE LIKE statement to copy the table schema, but it cannot copy the table data.

Here is a sample statement:

obclient>CREATE TABLE t1_like like t1;
Query OK, 0 rows affected

Create a duplicate table

Creating a duplicate table is an advanced tuning technique in OceanBase Database.

In general, OceanBase uses a three-replica architecture. By default, each partition of a table has three replicas in the OceanBase database, one of which works as the leader and the other two work as followers. By default, the leader provides write and read services.

You can create a duplicate table on every server in the tenant. The tenant can have one leader and two or more followers. Full data synchronization is implemented to maintain strong consistency between the leader and followers. This allows your application to execute specific SQL JOIN queries on the same server for better performance.

To create a duplicate table, add a DUPLICATE_SCOPE clause to the CREATE TABLE statement.

Here is a sample statement:

obclient>CREATE TABLE table_name (i_id int
, i_name varchar(24)
, i_price decimal(5,2)
, i_data varchar(50)
, i_im_id int
, primary key(i_id)) COMPRESS FOR QUERY pctfree=0 BLOCK_SIZE=16384
duplicate_scope='cluster' locality='F,R{all_server}@doc_1, F,R{all_server}@doc_2,F,R{all_server}@doc_3' primary_zone='doc_1';