Top 5 MySQL CREATE TABLE Syntax for T-SQL Developers

Total: 1 Average: 5

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. 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.

However, before we proceed, let’s clarify a few points:

  1. The MySQL version used here is MySQL 8 using the InnoDB storage engine.
  2. The SQL Server version used here is SQL Server 2019.

Sounds good? Let’s begin.

CodingSight - Top 5 MySQL CREATE TABLE Syntax for T-SQL Developers

1. MySQL CREATE TABLE Identifier Syntax

Let’s start the comparison by defining the table name.

MySQL Does Not Have a Schema Name in the Table Definition

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_name

Did 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.

MySQL Uses Backticks Instead of Square Brackets in Table and Column Names

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:

The backtick character is shown on a US keyboard layout.
Figure 1. The backtick character is shown 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.

MySQL Uses ‘TEMPORARY’ Instead of ‘#’ for Temporary Tables

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.

2. MySQL Column Definition 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:

The default value for ModifiedDate defined in CREATE TABLE works as expected after the INSERT statement.
Figure 2. The default value for ModifiedDate defined in CREATE TABLE works as expected after the INSERT statement.

3. MySQL Table Constraints Syntax

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 Keys

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 Keys

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 Keys

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.

4. MySQL Clustered and Non-Clustered Index

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.

5. 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.

Edwin Sanchez

Edwin Sanchez

Software developer and project manager with a total of 20+ years of software development. His most recent technology preferences include C#, SQL Server BI Stack, Power BI, and Sharepoint. Edwin combines his technical knowledge with his most recent content writing skills to help new breed of technology enthusiasts.