SQL Create Table

In the previous chapter, we created a database on a database server, like MySQL and SQL Server.

Now, we will create tables inside the database, which actually hold the data in the form of rows and columns.

Syntax

Here is the basic syntax to create a table.

  • The keyword CREATE TABLE creates a new table.
  • Data types define the type of values allowed by the columns.
  • Constraints define rules regarding the values allowed by the columns.
CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ....
);

Create Table

Execute the below SQL query, to create a new table with four columns id, name, birth_date, and phone.

  • Each column is defined with a name, data type, and its constraints.
  • Data types and constraints may vary between database servers, like MySQL, SQL Server, Oracle, etc.,

In the below example, the primary key auto-increment can be applied by using the IDENTITY keyword in SQL Server, which is different from MySQL.

-- Syntax for MySQL Database 
CREATE TABLE persons (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);
 
-- Syntax for SQL Server Database 
CREATE TABLE persons (
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

Create Table If Not Exists

We can use the below SQL query to create a table and avoid the error when the table already exists, by including the IF NOT EXISTS keyword.

CREATE TABLE IF NOT EXISTS persons (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

Describe Table

After the table is successfully created, we can find the table definition using the below SQL query.

  • Table definition includes both its structure and its column information.
-- Syntax for MySQL database
DESC table_name;

-- Syntax for SQL Server database
EXEC sp_columns table_name;

Show Tables

We can use the below SQL query to see the list of all the tables in a MySQL database.

SHOW TABLES;

Overall

We now know how to create a table inside a database.

Related Links