Category: Relational

3 Ways to Fix MySQL Error 1062 “Duplicate entry ‘…’ for key” When Adding a Primary Key to an Existing Table

Posted on by Ian

If you’re getting an error in MySQL that reads something like “Duplicate entry ‘1’ for key ‘person.PRIMARY’” when trying to add a primary key to an existing table, it’s probably because the table already contains duplicate values in the column you’re trying to add the primary key to.

When we add a primary key to an existing table, we need to make sure that the column/s that we’re adding the primary key to contains unique values across all of its rows.

One way to fix this issue is to create a composite primary key (one that’s defined across multiple columns). Another way to fix it is to change the values so that they’re all unique. Another option is to apply the primary key to a different column altogether (i.e. one that contains unique values).

Continue reading →

How the REPLACE Statement Deals with Missing Columns in MySQL

Posted on by Ian

When we use the REPLACE statement in MySQL, we have the option of replacing the values of all columns in the row or just certain columns.

If we choose to replace just certain columns, we need to be aware of how this will affect the outcome. The “missing columns” in the title of this article refers to when we specify a value for just some, but not all columns.

Continue reading →

How to Include Invisible Columns When Copying a Table in MySQL

Posted on by Ian

If you’ve ever tried to copy a table with invisible columns in MySQL, you may have been baffled when you realised that none of the invisible columns were copied. Or, you may have copied them, but they ended up VISIBLE instead of INVISIBLE on the new table.

Fortunately, there’s an easy fix to this issue. Actually, there are at least a couple of ways we can approach it.

Continue reading →

A Quick Overview of the REPLACE Statement in MySQL

Posted on by Ian

In MySQL, we can use the REPLACE statement to insert data if we think that some rows need to replace existing rows.

The REPLACE statement works just like the INSERT statement, except that if it contains rows with the same PRIMARY KEY or UNIQUE index as those in the table, then it replaces those rows in the table. It does this by deleting the old row and then inserting the new row.

To use the REPLACE statement, we must have both the INSERT and DELETE privileges for the table.

Continue reading →

How to Select Data from an Invisible Column in MySQL

Posted on by Ian

From MySQL 8.0.23 we’ve had the ability to create invisible columns. But with that comes a potential “gotcha” with our existing queries, and even with new queries we write, depending on how we write them.

The issue is that invisible columns are not returned whenever we use the asterisk wildcard (*) to select data. One of the most common ways to start a query is with SELECT *. This typically selects all columns from the table.

But it doesn’t select invisible columns.

So if we want to include invisible columns, we need to explicitly include them in our SELECT list.

Continue reading →

How to Fix the “A table must have at least one visible column” Error in MySQL

Posted on by Ian

If you’re getting an error that reads “A table must have at least one visible column” in MySQL, it could be that you’re trying to create a table with nothing but invisible columns. Or you could be altering an existing column to be invisible, but it would leave the table with nothing but invisible columns.

To fix this issue, be sure to have at least one visible column in the table.

Continue reading →

Why ‘FORMAT=TRADITIONAL’ Fails but ‘explain_format=TRADITIONAL’ Succeeds When Running EXPLAIN ANALYZE in MySQL

Posted on by Ian

If you’ve been using EXPLAIN ANALYZE to analyse your MySQL queries, you may have encountered a little quirk regarding the output format: When we explicitly use FORMAT=TRADITIONAL in our EXPLAIN ANALYZE statement, we get an error. But when our explain_format system variable is set to TRADITIONAL we don’t get an error.

What’s going on with that? Is this a bug?

First up, no it’s not a bug. It can all be explained by the way EXPLAIN ANALYZE handles our explain_format setting.

Continue reading →

How to Insert Data into an Invisible Column in MySQL

Posted on by Ian

Invisible columns allow us to hide columns from certain SQL queries, such as those that use the asterisk wildcard (*) to implicitly select all columns. But along with this comes some implications for when we want to insert data into those columns.

If we want to insert data into an invisible column, we need to explicitly name that column in the column list. We can’t use a blank column list to insert data into all columns like we can with visible columns. If we try to do that, we’ll get an error. That said, we can omit the invisible column from the column list in order to have its default value inserted.

Continue reading →