DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 CREATE TABLE

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.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 DELETE
Next Db2 Identity Column

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.