Create a table

You can use the CREATE TABLEstatement to create a table.

This topic describes the creation of non-partitioned tables. For more information about the creation of partitioned tables, see create a partitioned table.

Create a non-partitioned table

A non-partitioned table is a table that has only one partition.

For example, the following statements each creates 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 (0.09 sec)

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 (0.10 sec)

In the preceding example, two tables are created. Some constraints, including primary keys and a foreign key, are defined on different columns. For more information about primary key and foreign key, see define column constraints.

The SQL data types are different in MySQL mode and Oracle mode. Make sure you use valid data types when you create tables. For more information about data types, see OceanBase Database SQL Reference (MySQL Mode) and OceanBase Database SQL Reference (Oracle Mode). Note

• To ensure performance and facilitate maintenance, we recommend that you specify a primary key or a unique key when you create a table. If no field can be used as the primary key, in MySQL mode, the system generates an auto-increment column as the hidden primary key column after table creation. In Oracle mode, you can add a number column as the primary key and use the serial numbers of the Oracle tenant to fill in this column. For more information about sequences, see manage sequences.

• You cannot add a primary key to a table by using the ALTER TABLE statement. Therefore, you must specify the primary key when you create a table.

Create a table by replicating the data in an existing table

• MySQL mode

  In MySQL mode, you can use the CREATE TABLE AS SELECT statement to copy the data in a table to a new table, but the table structure may change, and the new table does not have the constraints, indexes, default values, and partition information of the source table.

  Sample statement:

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

  You can also use the CREATE TABLE LIKE statement to copy a table structure, but not the data in the table.

  Sample statement:

  obclient>CREATE TABLE t1_like like t1;
  Query OK, 0 rows affected (0.11 sec)
  

• Oracle mode

  In Oracle mode, you can use the CREATE TABLE AS SELECT statement to copy the data and data types to a new table, but constraints, such as NOT NULL, and indexes are not copied.

  Sample statement:

  obclient>CREATE TABLE t2_copy AS SELECT * FROM t2;
  Query OK, 0 rows affected (0.10 sec)
  

  The CREATE TABLE LIKE statement is not supported in Oracle mode.

Create a replica table

Replica tables are an advanced tuning technique in OceanBase Database.

In general, OceanBase uses a three-replica architecture. By default, each partition of a table exists in three replicas, one of which is the leader, and the other followers. By default, the leader provides write and read services.

You can create a replica table on every server in the tenant. You can have only one leader replica and two or more follower replicas in the tenant. Full synchronization is used to maintain the strong consistency between the leader and the followers. The enables your application to execute some SQL JOIN queries within the same node for better performance.

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

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';