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.
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.
CREATE TABLE table_name( column1 datatype, column2 datatype,... column-N datatype, PRIMARY KEY(one or more column) );
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.
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.
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") );
Let’s look at another example where we will use multiple columns in the primary key.
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.
CREATE TABLE "test.customer"( "CustomerId" integer NOT NULL, "CustomerName" character varying(45), "ProductId" character varying(45), "Country" character varying(45), PRIMARY KEY ("CustomerId","ProductId") );
If you’ve enjoyed this tutorial and our broader community, consider checking out our DigitalOcean products which can also help you achieve your development goals.