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.
Category: MySQL
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 readingQuick Intro to SHOW CREATE DATABASE in MySQL
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).
2 Reasons Your MySQL REPLACE Statement Results in Duplicate Rows
If you’re using the REPLACE statement to replace rows in MySQL, but it’s inserting duplicate rows instead, it could be due to one of the following reasons.
A Quick Overview of the SHOW CREATE TABLE Statement in MySQL
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.
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.
A Quick Intro to the ‘sql_quote_show_create’ System Variable in MySQL
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 readingCreate 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.
A Quick Overview of the MySQL DO Statement
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 readingHow 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