Ian – Page 97

A Quick Overview of the ‘show_gipk_in_create_table_and_information_schema’ System Variable in MySQL

March 20, 2024 by Ian

Depending on our configuration, if we create a table without a primary key column, MySQL will automatically create one behind the scenes. This is called a generated invisible primary key (GIPK).

We can normally use statements such as SHOW CREATE TABLE, SHOW COLUMNS, SHOW INDEX or even check the information schema to see if a table has a GIPK.

But this depends on the setting of our show_gipk_in_create_table_and_information_schema system variable.

This variable can be set to ON or OFF. When set to ON, we will see GIPKs in the output of the aforementioned statements. When set to OFF, we won’t see any GIPKs.

Understanding MySQL’s ‘sql_generate_invisible_primary_key’ System Variable

March 19, 2024 by Ian

One of the more recent additions to MySQL’s list of system variables is the sql_generate_invisible_primary_key variable. This variable was introduced in MySQL 8.0.30 along with the introduction of generated invisible primary keys (GIPKs).

The purpose of this variable is to allow us to specify whether or not the system will generate a GIPK when we omit a primary key from a table’s definition when creating the table.

By default the sql_generate_invisible_primary_key is set to OFF, which basically means that GIPKs are disabled by default. But we can change this to ON in order to enable GIPKs.

Using the ‘key_column_usage’ Table to Get the Primary Key and Foreign Keys in MySQL

March 18, 2024 by Ian

In MySQL we can use the information_schema.key_column_usage table to get information about the keys in a table. This table returns one row for each column that is constrained as a key.

We can use this table to find out which column/s is the primary key of a given table and to return any foreign keys from the table.

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

March 17, 2024 by Ian

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.

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

March 16, 2024 by Ian

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.

What is a Generated Invisible Primary Key (GIPK) in MySQL?

August 9, 2024March 15, 2024 by Ian

While primary keys are generally considered a necessity when it comes to relational databases, they’re usually included in the SQL code that creates the database table.

MySQL 8.0.30 introduced generated invisible primary keys which provide an alternative to explicitly specifying a primary key for a table.

A generated invisible primary key (GIPK) is a primary key that’s created implicitly by the MySQL server. If we create a table without an explicit primary key, the MySQL server automatically creates a generated invisible primary key for us (assuming it’s an InnoDB table and that GIPKs are enabled).

Add a Primary Key to an Existing Column in MySQL

August 9, 2024March 14, 2024 by Ian

MySQL allows us to easily add a primary key constraint to an existing column. We can use the ALTER TABLE statement to achieve this outcome.

We can also use the ADD PRIMARY KEY clause, which can be useful for adding composite primary keys.

How to Create an Invisible Column in MySQL

March 13, 2024 by Ian

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

Quick Intro to SHOW CREATE DATABASE in MySQL

March 12, 2024 by Ian

MySQL has a SHOW CREATE DATABASE statement that shows the CREATE DATABASE statement that would be used to recreate the database.

We can use the SHOW CREATE DATABASE statement to create an identical database on another server, or to create a similar database on the same server (while changing the name).

We can alternatively use SHOW CREATE SCHEMA to do the same thing (SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE).

What is a Composite Primary Key?

August 9, 2024March 11, 2024 by Ian

Primary keys are a crucial part of relational database management systems (RDBMSs). Most of the time we create a primary key constraint against a single column that is the unique identifier for the table.

But we can also create primary key constraints against more than one column. When we do this, we call it a composite primary key.

Composite keys can be handy when we don’t have a single column that contains unique values, but multiple columns can be combined to create a unique value.