// Tutorial //

SQL Create Table

Published on August 3, 2022
Default avatar
By Meghna Gangwar
Developer and author at DigitalOcean.
SQL Create Table

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

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.

SQL Create Table MySQL
SQL Create Table - MySQL

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 - PostgreSQL
SQL Create Table - PostgreSQL

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.

SQL Create Table - Multiple Column Primary Key - MySQL
SQL Create Table - Multiple Column Primary Key - MySQL

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")
);
SQL Create Table On PostgreSQL With Multiple Column Primary Key
SQL Create Table - Multiple Column Primary Key - PostgreSQL

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar
Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?