Tutorial

SQL Create Table

Published on August 3, 2022
Default avatar

By Meghna Gangwar

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

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar
Meghna Gangwar

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel