Summary: in this tutorial, you will learn how to create a new table in the database by using the Db2
CREATE TABLE statement.
Introduction to the Db2
CREATE TABLE statement
Tables are logical objects in Db2 that stores data. Tables are made up of columns and rows, like a spreadsheet. The rows of a table do not have a specified order. However, the columns of the table have the order that is specified when the table created.
A column in a table is associated with a specific data type e.g., character string, numeric, or temporal. A column always stores values of the same type. A table can have one or more columns. Typically, a table contains multiple columns.
To create a new table, you use the
CREATE TABLE statement. The following shows the simplified syntax of the
CREATE TABLE statement:
CREATE TABLE [schema_name.]table_name ( column_1 data_type NOT NULL, column_2 data_type DEFAULT value, column_3 data_type CHECK(expression), ..., table_constraints );
In this syntax:
- First, specify the name of the table that you want to create. The name of the table must be unique within the schema. You can optionally specify the schema to which the table belongs.
- Second, specify a list of columns of the table. Each column is associated with a specific data type and may have constraint such as
- Third, specify the table constraint such as
CHECKconstraints. Note that you’ll learn more about these constraints in the next tutorial.
CREATE TABLE example
The following example uses the
CREATE TABLE statement to create a new table named
CREATE TABLE stores( store_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, store_name VARCHAR(150) NOT NULL, address_line_1 VARCHAR(255) NOT NULL, address_line_2 VARCHAR(100), city_id INT NOT NULL, state_id INT NOT NULL, zip_code VARCHAR(6), PRIMARY KEY (store_id) );
store_idcolumn is an integer column. The
GENERATED BY DEFAULT AS IDENTITYclause marks the
store_idcolumn as an identity column so that when you insert a new row into the
storestable, Db2 will automatically generate a sequential integer for the
NOT NULLconstraint ensures that the
store_idwill not accept any NULL value.
store_nameis a varying character (
VARCHAR) column with a maximum length of 150. It has a
NOT NULLconstraint that will enforce non-null values.
address_line_1is also a varying character column with a maximum length of 255 and does not accept NULL.
address_line_2is a varying character column with a maximum length of 100. The
address_line_2is a nullable column so it can store NULL values.
state_idare integer columns. They accept NULL values.
zip_codecolumn is a varying character column with a maximum length of 6. It is a nullable column.
store_idis the primary key column of the
storestable specified by the
PRIMARY KEYconstraint at the end of the statement. It means that the store_id will store unique values that identify all rows of the table.
After executing the
CREATE TABLE statement, you will find the
stores table appears in the database catalog. The
stores table is empty once created. You can view its content by using the following
SELECT * FROM stores;
To add new rows to the
stores table, you use the
In this tutorial, you have learned how to use the Db2
CREATE TABLE statement to create a new table in a database.