You can execute the CREATE TABLE statement to create a table.
For information about how to create partitioned tables, see Create a partitioned table.
Create a non-partitioned table
A non-partitioned table is a table that has only one partition.
Example:
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
In the preceding example, two tables are created. Some constraints, including the primary keys and foreign keys, are defined on different columns. For more information about primary keys and foreign keys, see Define column constraints.
Pay attention to data types when you create table columns. For more information about SQL data types, see Data types.
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 column can be used as the primary key, the system generates an auto-increment column as the hidden primary key column after table creation. For more information about auto-increment columns, see Define an auto-increment column.
Create a table by copying the data in an existing table
Copy table data
You can execute the CREATE TABLE AS SELECT statement to copy data from a table. The constraints, indexes, default values, and partitions are lost when you copy the data.
Example:
obclient>CREATE TABLE t1_copy AS SELECT * FROM t1;
Query OK, 3 rows affected
Copy the table schema
You can also execute the CREATE TABLE LIKE statement to copy the schema but not the data of a table.
Example:
obclient>CREATE TABLE t1_like like t1;
Query OK, 0 rows affected