Author: Ian

A Quick Overview of the SHOW CREATE TABLE Statement in MySQL

Posted on by Ian

In MySQL, we can use the SHOW CREATE TABLE statement to produce a CREATE TABLE statement from an existing table.

This enables us to generate a script that we can use to recreate the table on another database, or on the same database at a later date. Or we could use it to create another similar table. We would need to modify the code accordingly when doing this, but it could give us a good starting point.

We can also use SHOW CREATE TABLE for a quick way to take a look at the table’s structure, and check for things like data types, primary keys, foreign keys, etc.

Continue reading →

Fix “Multiple primary key defined” Error in MySQL

Posted on by Ian

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 →

A Quick Intro to the ‘sql_quote_show_create’ System Variable in MySQL

Posted on by Ian

MySQL provides the sql_quote_show_create system variable, which allows us to specify whether or not to include quotes around identifiers when using the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

By default, this variable is set to 1, which means that quotes/backticks will be generated whenever these statements are run.

However, we might not always want quotes or backticks around the object names, and so we can change the value of this variable to disable quoted identifiers.

Continue reading →

Create a Composite Primary Key in MySQL

Posted on by Ian

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 →

A Quick Overview of the MySQL DO Statement

Posted on by Ian

In MySQL we can use the DO statement if we want to execute an expression without getting a result set.

DO works in a similar way to the SELECT statement, but without returning a result set like SELECT normally would. DO can also be slightly faster, given it doesn’t return a result set.

This could be useful in stored functions or triggers that don’t allow us to run statements that return a result set.

Continue reading →

How to Create a Primary Key in MySQL

Posted on by Ian

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

Posted on by Ian

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 →

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 →