SQL Create Table

When we have to store data in relational databases, the first part is to create the database. Next step is to create a table in the database that will store our data. In this tutorial, we will discuss how to create a table using SQL queries in MySQL and PostgreSQL databases.

I am not covering SQL Server create table examples, because they are similar to PostgreSQL queries.

SQL Create Table

In order to store data in a table, it is very important to understand the type of data that needs to be stored. Let us try to understand the syntax for creating a table.

SQL Create Table Syntax

CREATE TABLE table_name( column1 datatype, column2 datatype,... column-N datatype, PRIMARY KEY(one or more column) );

• CREATE TABLE is the keyword to tell the database to create a table.
• table_name is the unique name that is used for the table.
• The brackets that are next to the table name contains the list of columns.
• The list contains the column name and the data type that can be stored in the respective columns.
• PRIMARY KEY is used to specify the columns to be used for primary key.

SQL Create Table with one column Primary Key

When we create a table, we have to provide the primary key information along with the column structure. Let’s look at some example to create a table with a single column as the primary key.

MySQL

CREATE TABLE `test`.`student` (
`studentId` INT NOT NULL,
`studentName` VARCHAR(45) NULL,
`State` VARCHAR(45) NULL,
`Country` VARCHAR(45) NULL,
PRIMARY KEY (`studentId`),
UNIQUE INDEX `studentId_UNIQUE` (`studentId` ASC) VISIBLE);

Above query will create a new table “Student” with the primary key column as “studentId”. Notice that every column name has a data type defined. For example, we can store only INT data in the studentId column whereas we can store VARCHAR data in the studentName column. VARCHAR(45) means that the maximum size of the string data allowed is 45 characters. Since the primary key can’t be null, we specify it in the studentId column definition.

PostgreSQL

We can create a table in PostgreSQL database using the following query.

CREATE TABLE "test.student"(
"StudentId" integer NOT NULL,
"StudentName" character varying(45),
"State" character varying(45),
"Country" character varying(45),
PRIMARY KEY ("StudentId")
);

SQL Create Table with Multiple Primary Keys

Let’s look at another example where we will use multiple columns in the primary key.

MySQL

CREATE TABLE `test`.`customer` (
  `CustomerId` INT NOT NULL,
  `CustomerName` VARCHAR(45) NULL,
  `ProductId` VARCHAR(45) NOT NULL,
  `State` VARCHAR(45) NULL,
PRIMARY KEY (`CustomerId`, `ProductId`),
UNIQUE INDEX `CustomrId_UNIQUE` (`CustomerId` ASC) VISIBLE);

Above query will create “customer” table in the “test” database schema. The primary key of this table is the combination of CustomerId and ProductId.

PostgreSQL

CREATE TABLE "test.customer"(
"CustomerId" integer NOT NULL,
"CustomerName" character varying(45),
"ProductId" character varying(45),
"Country" character varying(45),
PRIMARY KEY ("CustomerId","ProductId")
);