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:
1 2 3 4 5 6 7 | 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
NOT NULLandCHECKconstraint. - Third, specify the table constraint such as
PRIMARY KEY,FOREIGN KEYandCHECKconstraints. 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:
1 2 3 4 5 6 7 8 9 10 | 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) ); |
In this stores table:
- The
store_idcolumn is an integer column. TheGENERATED BY DEFAULT AS IDENTITYclause marks thestore_idcolumn as an identity column so that when you insert a new row into thestorestable, Db2 will automatically generate a sequential integer for thestore_idcolumn. TheNOT NULLconstraint ensures that thestore_idwill not accept any NULL value. - The
store_nameis a varying character (VARCHAR) column with a maximum length of 150. It has aNOT NULLconstraint that will enforce non-null values. - The
address_line_1is also a varying character column with a maximum length of 255 and does not accept NULL. - The
address_line_2is a varying character column with a maximum length of 100. Theaddress_line_2is a nullable column so it can store NULL values. - The
city_idandstate_idare integer columns. They accept NULL values. - The
zip_codecolumn is a varying character column with a maximum length of 6. It is a nullable column. - The
store_idis the primary key column of thestorestable specified by thePRIMARY 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 statement:
1 | SELECT * FROM stores; |
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.