Are you a T-SQL developer learning the basics of MySQL? Then, one of the things you might want to learn is MySQL CREATE TABLE statement. Besides, the fastest way to learn a new SQL database platform is by comparing its common functionality and syntax.
That’s what we are going to do today. But the full syntax is a lot. So, we will only cover 5 basic points to get you up and running with MySQL CREATE TABLE command.
However, before we proceed, let’s clarify a few points:
- The MySQL version used here is MySQL 8 using the InnoDB storage engine.
- The SQL Server version used here is SQL Server 2019.
Sounds good? Let’s begin.
Create Table Statement in MySQL and T-SQL
Let’s start the comparison by defining the table name.
One of the noticeable syntax differences in MySQL CREATE TABLE is the absence of a schema name. Here’s how it goes:
CREATE TABLE database_name.table_nameDid you notice the table name preceded by the database name? Now, here’s a T-SQL equivalent format you are familiar with:
CREATE TABLE database_name.schema_name.table_name
Why? Because in MySQL, a schema is synonymous with a database. The database and schema names are optional. However, if you need to create a table in another database in the same script, this is a must.
That’s right. Using square brackets in table and column names will trigger an error in MySQL. You should use a pair of backticks to enclose identifiers instead. Figure 1 shows where you find it on a US keyboard layout:
Now, check out the sample:
-- MySQL CREATE TABLE
CREATE TABLE `testdatabase`.`person` (
`BusinessEntityID` INT NOT NULL,
`PersonType` NCHAR(2) NOT NULL,
`Title` VARCHAR(8) NULL,
`FirstName` NVARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NULL,
`LastName` VARCHAR(50) NOT NULL,
`Suffix` NVARCHAR(10) NULL,
`EmailPromotion` INT NOT NULL,
`ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),
PRIMARY KEY (`BusinessEntityID`));
From the code sample above, table and column name identifiers are enclosed in backticks. If you are not used to enclosing table and column names with this, trust me, you are not alone.
Check the equivalent code in T-SQL below:
-- T-SQL CREATE TABLE
CREATE TABLE [testdatabase].[dbo].[Person](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL,
[Suffix] [nvarchar](10) NULL,
[EmailPromotion] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL DEFAULT GETDATE(),
CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY
(
[BusinessEntityID] ASC
)
) ON [PRIMARY]
Of course, you don’t need to enclose these identifiers. But if you use reserve words or space in table or column names, an error will occur.
How to Create Temporary Table
Fond of temporary tables? Then MySQL will let you type more to define a temporary table. Check out the sample:
-- MySQL Temporary Table
CREATE TEMPORARY TABLE `MyTempTable`
Meanwhile, T-SQL coders are used to the # symbol before the table name. See the equivalent code from the above:
-- T-SQL Temporary Table
CREATE TABLE #MyTempTable
We’re done with table definitions and names. Let’s proceed with column definitions.
MySQL Column Syntax
You can’t have a basic SQL table without columns. So, what similarities and differences are there between MySQL and SQL Server?
Column Names and Data Types
You already know about the backticks. Anything else basic about column names and data types is the same in MySQL and T-SQL. See the sample below:
CREATE TABLE `testdatabase`.`people`
(
`ID` INT,
`LastName` VARCHAR(50),
...
);
However, there’s more.
Column Nullability
Columns can be nullable or not. You can do that by adding NULL or NOT NULL in the column definition.
Let’s expand on our previous example.
CREATE TABLE `testdatabase`.`people`
(
`ID` INT NOT NULL,
`LastName` VARCHAR(50) NOT NULL,
`FirstName` VARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NULL,
...
);
It’s pretty much the same as T-SQL. How about default values?
Column Default Values
Up next are the column default values. These are handy when you insert a record but did not specify a specific value for a column. Let’s expand our previous example some more:
CREATE TABLE `testdatabase`.`people`
(
`ID` INT NOT NULL,
`LastName` VARCHAR(50) NOT NULL,
`FirstName` VARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NULL,
`ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),
...
);
The ModifiedDate column defaults to the current system date and time. If you do an INSERT on this table like the one below, there will always be a value for ModifiedDate.
INSERT INTO testdatabase.people
(ID, LastName, FirstName, MiddleName)
VALUES
(1,'Kirk','James','Tiberius');
It works as expected after the INSERT. Here’s a screenshot from dbForge Studio for MySQL:
MySQL Create Table with Primary, Foreign and Unique Keys
Like in any SQL platform, you can define rules on table column data using constraints in MySQL. As this is important for table design, we need to consider this next point.
A T-SQL developer should feel right at home with MySQL primary keys, foreign keys, and unique keys.
Primary Key
You can define one or more columns to be the primary key. The syntax is almost the same as in SQL Server. Any exceptions? Yes. It is the use of clustered and non-clustered indexes. But let’s reserve the discussion of this one in the next section.
For now, we focus on the syntax of primary keys. Here is the same example we had earlier, but we add the ID column as the primary key:
CREATE TABLE `testdatabase`.`people`
(
`ID` INT NOT NULL PRIMARY KEY,
`LastName` VARCHAR(50) NOT NULL,
`FirstName` VARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NULL,
`ModifiedDate` DATETIME NOT NULL DEFAULT NOW()
);
Easy, isn’t it?
But what if you need multiple columns? Like in T-SQL, you have 2 options. Check the examples below:
-- OPTION 1: Let MySQL generate the constraint name
CREATE TABLE `testdatabase`.`people`
(
`ID` INT NOT NULL,
`LastName` VARCHAR(50) NOT NULL,
`FirstName` VARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NOT NULL DEFAULT '',
`ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),
PRIMARY KEY (`LastName`,`FirstName`,`MiddleName`)
);
-- OPTION 2: Specify your constraint name
CREATE TABLE `testdatabase`.`people`
(
`ID` INT NOT NULL,
`LastName` VARCHAR(50) NOT NULL,
`FirstName` VARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NOT NULL DEFAULT '',
`ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),
CONSTRAINT `pk_name` PRIMARY KEY (`LastName`, `FirstName`, `MiddleName`)
);
That’s it for primary keys.
Foreign Key
Another important constraint is the foreign key. This is used for cross-referencing a table to another table:
CREATE TABLE `testdatabase`.`address`
(
`ID` int NOT NULL PRIMARY KEY,
`parent_id` int NOT NULL,
`full_address` varchar(100) NOT NULL,
CONSTRAINT `FK_address_parent_id` FOREIGN KEY (`parent_id`)
REFERENCES `people` (`ID`)
);
Did you see the similarity with T-SQL?
Unique Key
Sometimes, you want to make sure that the data inserted into a table is unique. MySQL also supports unique keys. Here’s an example:
CREATE TABLE `testdatabase`.`people`
(
`ID` INT NOT NULL,
`LastName` VARCHAR(50) NOT NULL,
`FirstName` VARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NOT NULL DEFAULT '',
`ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),
CONSTRAINT `PK_people_id` PRIMARY KEY (`ID`),
CONSTRAINT `IDX_name` UNIQUE KEY (`LastName`,`FirstName`,`MiddleName`)
);
The above code will ensure that only unique names will be added to the table.
Create Table Index Examples (Clustered and Non-Clustered)
In the previous examples, you have seen primary and secondary keys created. But where are clustered and non-clustered indexes? That’s noticeable because in SQL Server you explicitly specify the keyword CLUSTERED to make a clustered index.
The interesting part is that MySQL does not have CLUSTERED or NONCLUSTERED keywords. How do you create clustered indexes?
Simple. Just specify the column(s) as the primary key, and the InnoDB storage engine will make that a clustered index. In the below example, ID is the primary key AND the clustered index.
CREATE TABLE `testdatabase`.`people`
(
`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`LastName` VARCHAR(50) NOT NULL,
`FirstName` VARCHAR(50) NOT NULL,
`MiddleName` VARCHAR(50) NOT NULL DEFAULT '',
`ModifiedDate` DATETIME NOT NULL DEFAULT NOW(),
CONSTRAINT `IDX_name` UNIQUE KEY (`LastName`,`FirstName`,`MiddleName`)
);
If you don’t specify a primary key, any unique index will be used as the clustered index.
Then again, if you don’t have a primary key and a unique index, InnoDB creates a hidden clustered index. Check the proof here.
The next question is, how about a non-clustered index?
If you already have a primary key as a clustered index, any other indexes will be non-clustered or secondary. Besides, you can only have one clustered index.
The next section will discuss a unique syntax for MySQL CREATE TABLE not found in T-SQL.
MySQL Table Cloning and Copying Syntax
Let’s discuss the table cloning. We can do it in MySQL CREATE TABLE. Then, we will show the T-SQL equivalent.
The example below creates the table with the same structure as the first table using CREATE TABLE…LIKE.
CREATE TABLE `people2` LIKE `people`We have just cloned the people table into people2. However, it is only the structure of the people table that has been copied, not the data. It also copied the key constraints and indexes of the original table. Quite handy, if you ask me.
The closest T-SQL equivalent is this:
-- T-SQL way of cloning a table. The indexes & key constraints are not included,
-- though
SELECT * INTO people2
FROM people
WHERE 1=0 -- this will cause the SELECT to return an empty result set.
But what if you want the data? Then, CREATE TABLE…SELECT is the answer:
CREATE TABLE `people3` AS
SELECT * FROM `people`;
Whatever data is in the people table, it will be copied to the people3 table. However, this command will not include indexes and constraints.
Now, the closest T-SQL equivalent is this:
-- T-SQL table copying
SELECT * INTO people3
FROM people
Conclusion
I hope that the points above could get you up and running with using MySQL CREATE TABLE from a perspective of a SQL Server T-SQL developer.
Some of you will prefer to use graphical tools. But later, you will find yourself looking for a reference when you need to create work tables in your stored procedures or ad-hoc scripts. Or you’re simply the type seeking more control by typing everything. In any case, a one-stop-shop comparing the basic syntax can be a lifesaver.
If you like this article, feel free to share it on your favorite social media.
Happy coding, everyone.
Tags: mysql, t-sql statements Last modified: March 30, 2023
