Db2 CREATE TABLE

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
);
Code language: SQL (Structured Query Language) (sql)

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 NOT NULLand CHECK constraint.
• Third, specify the table constraint such as PRIMARY KEY, FOREIGN KEY and CHECK constraints. Note that you’ll learn more about these constraints in the next tutorial.

Db2 CREATE TABLE example

The following example uses the CREATE TABLE statement to create a new table named stores:

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)
);
Code language: SQL (Structured Query Language) (sql)

In this stores table:

• The store_id column is an integer column. The GENERATED BY DEFAULT AS IDENTITY clause marks the store_id column as an identity column so that when you insert a new row into the stores table, Db2 will automatically generate a sequential integer for the store_id column. The NOT NULL constraint ensures that the store_id will not accept any NULL value.
• The store_name is a varying character (VARCHAR) column with a maximum length of 150. It has a NOT NULL constraint that will enforce non-null values.
• The address_line_1 is also a varying character column with a maximum length of 255 and does not accept NULL.
• The address_line_2 is a varying character column with a maximum length of 100. The address_line_2 is a nullable column so it can store NULL values.
• The city_id and state_id are integer columns. They accept NULL values.
• The zip_code column is a varying character column with a maximum length of 6. It is a nullable column.
• The store_id is the primary key column of the stores table specified by the PRIMARY KEY constraint 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 statement:

SELECT * FROM stores;
Code language: SQL (Structured Query Language) (sql)

To add new rows to the stores table, you use the INSERT statement.

In this tutorial, you have learned how to use the Db2 CREATE TABLE statement to create a new table in a database.