How to Set and Check the ‘sql_quote_show_create’ System Variable in MySQL

In MySQL there’s a system variable called sql_quote_show_create which is used to determine whether or not to use quoted identifiers when using the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

By default sql_quote_show_create is set to 1, which means that quotes/backticks will be generated whenever these statements are run, but we can also change this value to 0 in order to disable quotes.

Below are examples of checking this variable and setting it.

Continue reading

4 Ways to Fix the “Failed to generate invisible primary key. Column ‘my_row_id’ already exists” Error in MySQL

If you’re getting an error that reads “Failed to generate invisible primary key. Column ‘my_row_id’ already exists“, it’s probably because you’re trying to create a table without a primary key, and you’ve named a column my_row_id.

When your system has generated invisible primary keys (GIPKs) enabled, and you create an InnoDB table without explicitly defining a primary key, MySQL automatically creates an invisible column called my_row_id and creates a primary key constraint against it. This is called a generated invisible primary key.

However, if you name one of your columns my_row_id, then MySQL can’t create the GIPK due to the column already existing.

There are several ways to go about fixing this issue.

Continue reading

How to Create an Invisible Column in MySQL

MySQL supports invisible columns (from MySQL 8.0.23), which means we can control whether a column is considered visible or invisible.

Invisible columns are hidden from queries that use the asterisk wildcard (*) in their SELECT list, but can be selected by explicitly naming the column.

We can create invisible columns when creating a table. We can also add invisible columns to existing tables. And we can modify existing columns to be invisible (and vice-versa).

Continue reading

Fix “Multiple primary key defined” Error in MySQL

If you’re getting an error that reads something like “Multiple primary key defined” in MySQL, it’s probably because you’re defining more than one primary key to a table.

A table can only have one primary key in MySQL.

It’s possible that you were trying to create a composite primary key (i.e. a primary key consisting of multiple columns), in which case, you can fix the issue with a simple modification to your code.

Otherwise, you will need to decide which column should be the primary key for the table and only apply the PRIMARY KEY definition to that column.

Continue reading

Create a Composite Primary Key in MySQL

A composite primary key is a primary key that consists of two or more columns. Together they will always provide a unique value within the table. In other words, the combination of both values will be unique across all rows – no two rows will share the same combined value.

In MySQL we can create a composite primary key with the PRIMARY KEY clause of the CREATE TABLE statement. We can also use the ADD PRIMARY KEY statement to add a composite primary key to an existing table that doesn’t already have a primary key.

Continue reading

How to Create a Primary Key in MySQL

Primary keys are a fundamental part of relational database management systems (RDBMSs). They help us to maintain data integrity.

We can create primary keys when we create the table, or we can add one later.

When we create the primary key with the table, we have the option of defining the key within the actual column definition, or as a separate clause after all column definitions.

Continue reading

Using the SET Clause of the REPLACE Statement in MySQL

In MySQL we can use the REPLACE statement to replace data in an existing table without inserting a new row. When we do this, we have a choice of syntax when it comes to selecting the row to replace.

One option (and probably the most common option) is to use the VALUES clause. Another option is to use the SET clause.

Below is an example of using the SET clause when using MySQL’s REPLACE statement.

Continue reading