The CREATE TABLE statement in SQL

Although, various database servers have tools for creating a table by using GUI/Wizards. There, you may give the database table a name, create columns with data types and may also set constraints like NOT NULL, Primary keys, auto-increment etc. (depending on the database server like MS SQL Server, MySQL, Oracle, DB 2 etc.)

The GUI or wizards may vary, depending on the provider; however, one common way of creating tables in different relational DBMS is using the CREATE Table statement of SQL.

An example/syntax of SQL CREATE Table statement is:

CREATE TABLE tbl_example(

ex_col1 INT NOT NULL,

ex_col2 VARCHAR(100),

ex_col3 CHAR(30),

ex_col4 datetime,

PRIMARY KEY (ex_col1)

);

A few points based on this SQL code:

  • In the above sample example or syntax of CREATE TABLE, you can see the process of creating a table starts with CREATE TABLE
  • This is followed by using the table name that may contain letters, numbers or characters like underscores.
  • Give the column names in that table and enclose in parenthesis.
  • The column names should also follow certain naming conventions.
  • With each column name, its data type is also provided along with constraint e.g. NOT NULL.
  • At the last, you may use the PRIMARY KEY statement for defining the primary key in that table. You may use one or more columns that are enclosed in parenthesis for a primary key.

The following section shows a few examples of creating tables. I will show you creating tables step by step and adding columns, constraints and primary keys etc.

Creating employees table by CREATE TABLE statement

In this example, we used the CREATE TABLE command for creating a table. I am using MS SQL Server for demonstration – so any figures shown below are from SQL Server database.

The query for creating the Sto_employee table:

CREATE TABLE Sto_employee(

ID INT NOT NULL,

emp_name VARCHAR(100),

emp_salary decimal(8,2),

emp_age tinyint,

join_date datetime

);

The above command should create a table, sto_employee. I used sto_ prefix for the table name as supposedly, this table is used to store the departmental store’s staff information.

A few columns with different data types are created, but no primary key is added to the table. So, if you view that table in the design mode, this is how it looks:

SQL CREATE TABLE

Adding a primary key in a table example

The primary key is created at the table level for uniquely identifying the rows. The column with primary key constraint cannot contain a NULL and duplicate value. In our above CREATE TABLE example, though we created ID with NOT NULL constraint, however, it may contain duplicate values; as this is not a primary key.

The SQL code below shows how to create the same table with ID as the primary key. The design view is also shown so you may see the difference:

CREATE TABLE Sto_employee(

ID INT NOT NULL,

emp_name VARCHAR(100),

emp_salary decimal(8,2),

emp_age tinyint,

join_date datetime

PRIMARY KEY (ID)

);

The table design view after executing this command:

SQL CREATE primary key

You can see the yellow icon for the ID field now. That represents a primary key in MS SQL Server.

Note:  A primary key may consist of one or more columns in a table. For using two or more columns, just separate column names by a comma. For example:

PRIMARY KEY (ID, emp_name)

Using Alter Table command for creating a primary key

Rather than first deleting the table and recreating again; as I did for above example to add the primary key, you may also use ALTER TABLE command for adding a primary key in the table.

The ALTER TABLE is a separate topic that is covered in its own tutorial, just for the sake of understanding, see an example below.

Suppose, we have created the table as in the first example. Now, we will add a primary key for the created table as follows:

ALTER TABLE sto_employee

ADD PRIMARY KEY (ID);

The ID column should be primary key in the sto_employee table after executing above command.

Creating a foreign key example

The FOREIGN KEY is used to link tables together by referring primary key. The table that contains the primary key (or candidate key) is called the parent table while the table that contains foreign key is called the child table.

Before showing you the command of creating a foreign key, consider the table created in above examples as the parent table.

Suppose, we need another table, sto_emp_salaries_paid that will be used for storing the salaries paid to employees every month.

As such, it may contain salaries of those employees only which data exists in the sto_employees table. So, we can use the ID of sto_employees in the sto_emp_salaries_paid table as a foreign key (Remember, the ID is the primary key in sto_employees).

Now, have a look at the CREATE TABLE command for sto_emp_salaries_paid table with a foreign key in MS SQL Server, MS Access, and Oracle databases:

CREATE TABLE sto_emp_salaries_paid (

    sal_id INT NOT NULL PRIMARY KEY,

    emp_id INT FOREIGN KEY REFERENCES sto_employee(ID),

    emp_sal_paid decimal(8,2),

    date_paid datetime,

);

The table with a foreign key should be created by above command. If you try entering an emp_id in the table that does not exist in the sto_employee table then an error should be generated.

Using SELECT INTO statement for creating/copying a table

In MS SQL Server, you may use the SELECT INTO command for copying the table structure and date from an existing table to the new table.

You may copy complete structure/data or specific columns and rows to the new table by using column names and WHERE clause in the SELECT INTO statement.

The following query shows an example of creating a new table with three columns from our example table, sto_employee.

The Query:

SELECT ID, emp_name, emp_age

INTO Sto_employee_2

FROM Sto_employee;

You may add the WHERE clause for only copying the specific rows in the new table from the existing table.

Note: This SQL code is applicable in SQL Server.

In Oracle database, you may use the CREATE TABLE…AS command for copying the existing table into the new table. An example query is:

CREATE TABLE Sto_employee_2 AS

                SELECT ID, emp_name, emp_salary

                FROM sto_employee;

An example query in the MySQL database for creating and copying the table data:

CREATE TABLE sto_employees_2

SELECT * FROM sto_employee;

This should create a new table and copy the data as well.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!