SQL statements to create database table SQL Server and MySQL

In this article, I will show you some simple SQL statements for creating a database and table for SQL Server and MySQL database engine.

I took the example from my Thailand administrative repository https://github.com/aaronamm/thai-administrative-division-province-district-subdistrict-sql

We start with creating a database and then creating a province table.

Create a database and table statements for SQL Server and MySQL

Create a database statement

SQL Server >

CREATE DATABASE Administrative;

MySQL >

CREATE DATABASE administrative;

Explain a statement

Create a database name administrative with default configuration.

Create a table statement

We create table with syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

SQL Server

CREATE TABLE [dbo].[Provinces] (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [Code] INT NOT NULL,
    [NameInThai] NVARCHAR(150) NOT NULL,
    [NameInEnglish] NVARCHAR(150) NOT NULL,
    CONSTRAINT [PK_Provinces_Id] PRIMARY KEY CLUSTERED ([ID])
);

Optional, we can also specify create index statement after creating table.

CREATE UNIQUE INDEX [UX_Provinces_Code] ON [dbo].[Provinces] ([Code] ASC);

MySQL

CREATE TABLE `provinces` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `code` INT NOT NULL,
    `name_in_thai` VARCHAR(150) NOT NULL,
    `name_in_english` VARCHAR(150) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ux_province_code` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

In MySQL we can't rename primary key name, it must always PRIMARY
Please refer to https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-indexes-keys

Explain a statement

We create table provinces.
Make id an primary key with key name, integer type and auto increment
A unique constraint becomes a unique index.
For MySQL, we specify database engine to InnoDB
For SQL Server, Id is clustered index and ProvinceCode is unique non-clustered index