SQL CREATE TABLE Statement

---

---

In an RDBMS (Relational Database Management System), database tables are used to store data in a structured way, using fields and records.

• A field is a column in the table, and it defines what kind of data will be stored (like names, dates, or numbers).
• A record is a row in the table, and it holds the actual data for each field.

In simple words, a table is made up of rows and columns, just like a spreadsheet. SQL provides us with different types of commands to work with this data easily. We can use SQL to:

• Create or delete tables
• Insert, update, or delete data in those tables

The SQL CREATE TABLE Statement

The CREATE TABLE statement in SQL is used to create a new table in an existing database.

When creating a table, you must define its structure by specifying a unique table name and listing all the column names along with their respective data types. These data types can include numbers, text, dates, etc. depending on the kind of data you want to store.

Syntax

Following is the basic syntax to create a table in SQL:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ...
  columnN datatype
);

Here:

• CREATE TABLE: It is the SQL command to create a new table.
• table_name: It is the name you want to give to your table.
• column1, column2, ...: These are the names of the columns you want in the table.
• datatype: Specifies the type of data that each column will store, such as INT, VARCHAR, DATE, etc.

Example

Let us create a table named CUSTOMERS with the following columns:

• ID: Customer ID (integer), not null
• NAME: Customer name (text), not null
• AGE: Customer age (integer), not null
• ADDRESS: Customer address (fixed length text)
• SALARY: Customer salary (decimal number)

CREATE TABLE CUSTOMERS (
   ID       INT NOT NULL,
   NAME     VARCHAR(20) NOT NULL,
   AGE      INT NOT NULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18, 2)
);

After executing the above command in a MySQL database, you get the following output:

Query OK, 0 rows affected (0.13 sec)

Verifying Table Creation

You can check if the table has been created successfully by listing all tables using the following SQL command:

SHOW TABLES;

If the CUSTOMERS table is listed, it means the table has been successfully created:

Also, you can use SQL DESC table_name command to list down the description of the table as follows:

DESC CUSTOMERS;

This will display the structure of the table created: column names, their respective data types, constraints (if any) etc. as shown below:

SQL CREATE TABLE with PRIMARY KEY

In SQL, you can use constraints like the PRIMARY KEY to make sure that a column (or a combination of columns) contains only unique and non-null values. It is used to uniquely identify each record in a table.

Syntax

Following is the syntax to create a table with a PRIMARY KEY in SQL:

Method 1: Define PRIMARY KEY directly on a column

CREATE TABLE table_name (
  column1 datatype PRIMARY KEY,
  column2 datatype,
  ...
);

Method 2: Define PRIMARY KEY separately for one or more columns

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ...
  columnN datatype,
  PRIMARY KEY (column_name)
);

You can use the second method to define a primary key on multiple columns (called a composite primary key) by listing more than one column inside the parentheses.

Example

Let us create the CUSTOMERS table again, this time with the ID column as the primary key:

CREATE TABLE CUSTOMERS (
   ID       INT NOT NULL PRIMARY KEY,
   NAME     VARCHAR(20) NOT NULL,
   AGE      INT NOT NULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18, 2)
);

After executing the above command, you will get the following output:

Query OK, 0 rows affected (0.07 sec)

SQL CREATE TABLE IF NOT EXISTS

Sometimes, you may try to create a table that already exists in the database. In such cases, SQL will return an error like this:

ERROR 1050 (42S01): Table 'CUSTOMERS' already exists

To avoid this error, you can use the CREATE TABLE IF NOT EXISTS command. This will create the table only if it doesn't already exist. If the table is already there, the command will simply do nothing and exit without any error.

Syntax

Following is the basic syntax of a CREATE TABLE IF NOT EXISTS statement:

CREATE TABLE IF NOT EXISTS table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

The following SQL command will create the CUSTOMERS table only if it doesn't already exist in the database otherwise it will exit without any error:

CREATE TABLE IF NOT EXISTS CUSTOMERS (
   ID       INT NOT NULL,
   NAME     VARCHAR(20) NOT NULL,
   AGE      INT NOT NULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18, 2),
   PRIMARY KEY (ID)
);

We get the output as shown below:

Query OK, 0 rows affected, 1 warning (0.02 sec)

CREATE TABLE Using Another Table

Instead of creating a completely new table from scratch, you can also create a table by copying the structure and/or data from an existing table. This is helpful when you want to clone or back up part of a table quickly.

Syntax

The basic syntax for creating a table from another table in SQL is as follows:

CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2...columnN]
FROM EXISTING_TABLE_NAME
WHERE Condition;

This command will:

• Create a new table with the same columns as the selected fields
• Copy all the matching data rows into the new table

Example

The following SQL command creates a new table called SALARY using two columns from the CUSTOMERS table: ID and SALARY:

CREATE TABLE SALARY AS
SELECT ID, SALARY
FROM CUSTOMERS;

After running this, the new SALARY table will have the following structure:

If you want to copy only the structure and not the data, you can add a condition that is always false as shown below:

CREATE TABLE SALARY AS
SELECT ID, SALARY
FROM CUSTOMERS
WHERE 1 = 0;

We get the output as shown below:

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Important Points for Creating a Table

Following are the important points to remember when creating a table in SQL:

• Table names should be meaningful and unique within the same database.
• Use clear and descriptive column names like FIRST_NAME (not 1stname), AGE, SALARY.
• Choose correct data types for each column. For example, use DECIMAL for currency, VARCHAR for names, and INT for IDs or ages.
• Always use PRIMARY KEY for identifying records uniquely.