login

Subscribe to SQL Tutorial websiteRSS Feed
 » SQL Tutorial » SQL CREATE TABLE

SQL CREATE TABLE

Summary: In this tutorial, you will learn how to use SQL CREATE TABLE statement to create new database tables.

Database table is a basic element of the database. Before storing data in a table you have to create it. SQL provides CREATE TABLE statement to allow you to do so. Here is the common syntax of SQL CREATE TABLE statement:

CREATE TABLE table_name(
Column_name1 data_type(data_length ),
Column_name2 data_type(data_length ),
..
)

There are four components you need to specify when creating a database table:

  • Database table name. It is suggested that the table name should be meaningful and in plural form of a noun. For example products is the name of a table which stores products data.
  • Name of each column in the table. You should name the column to be meaningful also and in a noun with singular form.
  • Data type for each column. You should choose the correct data type for each column in a table. The most common data types are text (varchar, nvarchar), numerical (smallint, int, bigint), date, time datetime, timespan,blob…You can refer supporting data type in the specific relational database management system's documentation.
  • Maximum length of each column: You have to specify the maximum length of data for each column. For example if you store product name, try to imagine the maximum length a product can have, such as 255 characters.

Beside those components above, you can have another additions for each column such as the column is mandatory or not (NULL or not NULL), default value of the column and value of the column is unique or not. If a column is unique and not null it can be a primary key of the table.

Here is an example of using SQL CREATE TABLE to create products table:

CREATE TABLE products (                                 
            ProductID int(11) NOT NULL AUTO_INCREMENT,            
            ProductName varchar(40) NOT NULL,                     
            SupplierID int(11) DEFAULT NULL,                      
            CategoryID int(11) DEFAULT NULL,                      
            QuantityPerUnit varchar(20) DEFAULT NULL,             
            UnitPrice decimal(19,4) DEFAULT NULL,                 
            UnitsInStock smallint(6) DEFAULT NULL,                
            UnitsOnOrder smallint(6) DEFAULT NULL,                
            ReorderLevel smallint(6) DEFAULT NULL,                
            Discontinued tinyint(4) NOT NULL,                     
            PRIMARY KEY (ProductID)
          )

SQL CREATE TABLE

In the above example, we've created a products table. The first column is ProductID which is the primary key of the product table which is used to differentiate between data rows or records. PRIMARY KEY keyword is used to specify the primary key of the table. There are also other columns used to describe a product of the products table with different data type, length and default value.

Once table is created, you can alter it by using SQL ALTER TABLE or removing it using SQL DROP TABLE.

Related Tutorials