{"id":478,"date":"2019-05-31T19:55:38","date_gmt":"2019-06-01T03:55:38","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=478"},"modified":"2020-04-11T06:59:20","modified_gmt":"2020-04-11T14:59:20","slug":"db2-create-table","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/","title":{"rendered":"Db2 CREATE TABLE"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to create a new table in the database by using the Db2 <code>CREATE TABLE<\/code> statement.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to the Db2 CREATE TABLE statement<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>To create a new table, you use the <code>CREATE TABLE<\/code> statement. The following shows the simplified syntax of the <code>CREATE TABLE<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> &#91;schema_name.]table_name (\n\tcolumn_1 data_type <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\tcolumn_2 data_type <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-keyword\">value<\/span>,\n\tcolumn_3 data_type <span class=\"hljs-keyword\">CHECK<\/span>(expression),\n\t...,\n\ttable_constraints\n);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>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.<\/li><li>Second, specify a list of columns of the table. Each column is associated with a specific data type and may have constraint such as <code><a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-not-null\/\">NOT NULL<\/a><\/code>and <a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-check-constraint\/\"><code>CHECK<\/code><\/a> constraint.<\/li><li>Third, specify the table constraint such as <code><a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-primary-key\/\">PRIMARY KEY<\/a><\/code>, <code><a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-foreign-key\/\">FOREIGN KEY<\/a><\/code>&nbsp;and <code><a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-check-constraint\/\">CHECK<\/a><\/code> constraints. Note that you&#8217;ll learn more about these constraints in the next tutorial.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Db2 CREATE TABLE example<\/h2>\n\n\n\n<p>The following example uses the <code>CREATE TABLE<\/code> statement to create a new table named <code>stores<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> stores(\n\tstore_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">GENERATED<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-keyword\">IDENTITY<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\tstore_name <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">150<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\taddress_line_1 <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\taddress_line_2 <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">100<\/span>),\n\tcity_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\tstate_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\tzip_code <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">6<\/span>),\n\tPRIMARY <span class=\"hljs-keyword\">KEY<\/span> (store_id)\n);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this <code>stores<\/code> table:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The <code>store_id<\/code> column is an integer column. The <code>GENERATED BY DEFAULT AS IDENTITY<\/code> clause marks the <code>store_id<\/code> column as an identity column so that when you <a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-insert\/\">insert a new row<\/a> into the <code>stores<\/code> table, Db2 will automatically generate a sequential integer for the <code>store_id<\/code> column. The <code>NOT NULL<\/code> constraint ensures that the <code>store_id<\/code> will not accept any NULL value.<\/li><li>The <code>store_name<\/code> is a varying character (<code>VARCHAR<\/code>) column with a maximum length of 150. It has a <code>NOT NULL<\/code> constraint that will enforce non-null values.<\/li><li>The <code>address_line_1<\/code> is also a varying character column with a maximum length of 255 and does not accept NULL.<\/li><li>The <code>address_line_2<\/code> is a varying character column with a maximum length of 100. The <code>address_line_2<\/code> is a nullable column so it can store NULL values.<\/li><li>The <code>city_id<\/code> and <code>state_id<\/code> are integer columns. They accept NULL values.<\/li><li>The <code>zip_code<\/code> column is a varying character column with a maximum length of 6. It is a nullable column.<\/li><li>The <code>store_id<\/code> is the primary key column of the <code>stores<\/code> table specified by the <code>PRIMARY KEY<\/code> constraint at the end of the statement. It means that the store_id will store unique values that identify all rows of the table.<\/li><\/ul>\n\n\n\n<p>After executing the <code>CREATE TABLE<\/code> statement, you will find the <code>stores<\/code> table appears in the database catalog. The <code>stores<\/code> table is empty once created. You can view its content by using the following <code>SELECT<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> stores;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To add new rows to the <code>stores<\/code> table, you use the <code><a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-insert\/\">INSERT<\/a><\/code> statement.<\/p>\n\n\n\n<p>In this tutorial, you have learned how to use the Db2 <code>CREATE TABLE<\/code> statement to create a new table in a database.<\/p>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"478\"\n\t\t\t\tdata-post-url=\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/\"\n\t\t\t\tdata-post-title=\"Db2 CREATE TABLE\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"478\"\n\t\t\t\tdata-post-url=\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/\"\n\t\t\t\tdata-post-title=\"Db2 CREATE TABLE\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you&#8217;ll learn how to use the Db2 CREATE TABLE statement to create a new table in a database.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":141,"menu_order":31,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-478","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Db2 CREATE TABLE: Creating a New Table Step by Step in Db2<\/title>\n<meta name=\"description\" content=\"In this tutorial, you&#039;ll learn how to use the Db2 CREATE TABLE statement to create a new table in a database.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Db2 CREATE TABLE: Creating a New Table Step by Step in Db2\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you&#039;ll learn how to use the Db2 CREATE TABLE statement to create a new table in a database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/\" \/>\n<meta property=\"og:site_name\" content=\"DB2 Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-11T14:59:20+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/\",\"url\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/\",\"name\":\"Db2 CREATE TABLE: Creating a New Table Step by Step in Db2\",\"isPartOf\":{\"@id\":\"https:\/\/www.db2tutorial.com\/#website\"},\"datePublished\":\"2019-06-01T03:55:38+00:00\",\"dateModified\":\"2020-04-11T14:59:20+00:00\",\"description\":\"In this tutorial, you'll learn how to use the Db2 CREATE TABLE statement to create a new table in a database.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.db2tutorial.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Db2 Basics\",\"item\":\"https:\/\/www.db2tutorial.com\/db2-basics\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Db2 CREATE TABLE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.db2tutorial.com\/#website\",\"url\":\"https:\/\/www.db2tutorial.com\/\",\"name\":\"DB2 Tutorial\",\"description\":\"A Comprehensive DB2 Tutorial\",\"publisher\":{\"@id\":\"https:\/\/www.db2tutorial.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.db2tutorial.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.db2tutorial.com\/#organization\",\"name\":\"Db2 Tutorial\",\"url\":\"https:\/\/www.db2tutorial.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png\",\"contentUrl\":\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png\",\"width\":500,\"height\":500,\"caption\":\"Db2 Tutorial\"},\"image\":{\"@id\":\"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Db2 CREATE TABLE: Creating a New Table Step by Step in Db2","description":"In this tutorial, you'll learn how to use the Db2 CREATE TABLE statement to create a new table in a database.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/","og_locale":"en_US","og_type":"article","og_title":"Db2 CREATE TABLE: Creating a New Table Step by Step in Db2","og_description":"In this tutorial, you'll learn how to use the Db2 CREATE TABLE statement to create a new table in a database.","og_url":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/","og_site_name":"DB2 Tutorial","article_modified_time":"2020-04-11T14:59:20+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/","url":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/","name":"Db2 CREATE TABLE: Creating a New Table Step by Step in Db2","isPartOf":{"@id":"https:\/\/www.db2tutorial.com\/#website"},"datePublished":"2019-06-01T03:55:38+00:00","dateModified":"2020-04-11T14:59:20+00:00","description":"In this tutorial, you'll learn how to use the Db2 CREATE TABLE statement to create a new table in a database.","breadcrumb":{"@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-create-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.db2tutorial.com\/"},{"@type":"ListItem","position":2,"name":"Db2 Basics","item":"https:\/\/www.db2tutorial.com\/db2-basics\/"},{"@type":"ListItem","position":3,"name":"Db2 CREATE TABLE"}]},{"@type":"WebSite","@id":"https:\/\/www.db2tutorial.com\/#website","url":"https:\/\/www.db2tutorial.com\/","name":"DB2 Tutorial","description":"A Comprehensive DB2 Tutorial","publisher":{"@id":"https:\/\/www.db2tutorial.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.db2tutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.db2tutorial.com\/#organization","name":"Db2 Tutorial","url":"https:\/\/www.db2tutorial.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/","url":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png","contentUrl":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png","width":500,"height":500,"caption":"Db2 Tutorial"},"image":{"@id":"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/478","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/comments?post=478"}],"version-history":[{"count":1,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/478\/revisions"}],"predecessor-version":[{"id":1074,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/478\/revisions\/1074"}],"up":[{"embeddable":true,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/141"}],"wp:attachment":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/media?parent=478"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}