MySQL Keys: Primary, Unique, Candidate, Foreign, Super Keys

MySQL Server is availed as a separate program and handles all the database instructions, commands, and statements including the processing algorithms leveraged by MySQL to process views and operations like drop, update, and show/list. This blog talks about the various salient features of MySQL Keys like the MySQL Primary Key, MySQL Unique Key, MySQL Candidate Key, MySQL Foreign Key, and MySQL Super Key and the various ways to define them.

1. What is a MySQL Primary Key?

According to the rules of a Relational Database, each table should have only one primary key. Apart from this, Primary Keys aren’t allowed to entertain duplicates (ND), null (NN), or no change (NC) values. 

If you try to update or insert NULL values to the Primary Key, it will throw an error. Apart from this, if the Primary Key contains multiple columns, the combination of values within these columns should be unique for it to be valid.

Since MySQL works swiftly with integers, the data type of the Primary MySQL Key column should be an integer. 

For instance, INT or BIGINT. It is suggested that you make sure that the value ranges for the Primary Key are enough for storing all the possible rows a table can have. 

In MySQL, this column generally has the AUTO_INCREMENT attribute that automatically generates a sequential integer whenever you supply a new row into the table.

Here are a couple of salient aspects of understanding MySQL Primary Key:

• Using Create Table to Define a Primary MySQL Key
• Using Alter Table to Define a Primary MySQL Key

1A) Using Create Table to Define a Primary MySQL Key

• Step 1: You can define the Primary MySQL Key for a table through the CREATE TABLE statement. In case the Primary MySQL Key has only one column, you can use the PRIMARY KEY constraint as a column constraint as follows:

CREATE TABLE table_name(
    primary_key_column datatype PRIMARY KEY,
    ...
);

• Step 2: If the Primary MySQL Key has more than one column, you would have to use the PRIMARY KEY constraint as a table constraint:

CREATE TABLE table_name(
    primary_key_column1 datatype,
    primary_key_column2 datatype,
    ...,
    PRIMARY KEY(column_list)
);

• Step 3: You can use the PRIMARY KEY table constraint if the Primary MySQL Key has only one column as follows:

CREATE TABLE table_name ( 
    primary_key_column datatype, 
    ...,
    PRIMARY KEY(primary_key_column)
);

• Step 4: In this example, you will be creating a table called users whose Primary MySQL Key is the user_id column:

CREATE TABLE users(
   user_id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40),
   password VARCHAR(255),
   email VARCHAR(255)
);

• Step 5: You can use the following statement to create a roles table that has the PRIMARY KEY constraint as the table constraint:

CREATE TABLE roles(
   role_id INT AUTO_INCREMENT,
   role_name VARCHAR(50),
   PRIMARY KEY(role_id)
);

• Step 6: If the Primary MySQL Key contains various columns, you need to specify what they are at the end of the CREATE TABLE statement. You need to put a comma-separated list of Primary Key columns within parentheses after the PRIMARY KEY keywords. In this example, you create a user_roles table whose Primary MySQL Key contains two columns: role_id and user_id. This table defines the PRIMARY KEY constraint as the table constraint:

CREATE TABLE user_roles(
   user_id INT,
   role_id INT,
   PRIMARY KEY(user_id,role_id),
   FOREIGN KEY(user_id) 
       REFERENCES users(user_id),
   FOREIGN KEY(role_id) 
       REFERENCES roles(role_id)
);

• Step 7: You can note here that this statement also ends up creating two Foreign Key constraints.

1B) Using Alter Table to Define a Primary MySQL Key

• Step 1: If due to some reason, a table does not have a Primary Key, you can leverage the ALTER TABLE statement to supply a Primary Key to the table as mentioned in the code snippet below:

ALTER TABLE table_name
ADD PRIMARY KEY(column_list);

• Step 2: The example mentioned below supplies the id column to the Primary Key. But first, you need to create the pkdemos table without a Primary Key:

CREATE TABLE pkdemos(
   id INT,
   title VARCHAR(255) NOT NULL
);

• Step 3: Next, you need to add the Primary Key to the table by leveraging the ALTER TABLE statement as follows:

ALTER TABLE pkdemos
ADD PRIMARY KEY(id);

• Step 4: If you want to add a Primary Key to a table that already has data, then, the data in the column(s) which will be encapsulated within the PRIMARY KEY must be unique and NOT NULL.

Dropping a PRIMARY KEY Constraint

Use the following SQL command to drop a PRIMARY KEY Constraint

ALTER TABLE Persons
DROP PRIMARY KEY;

2. What is a MySQL Unique Key?

A group of one or more table fields/columns that uniquely identify a record in a database table is known as a unique key in MySQL Keys.

It’s similar to a primary key in that it can only accept one null value and cannot have duplicate values.

Both the unique key and the primary key ensure that a column or set of columns is unique.

Here are a couple of salient aspects of understanding Unique Key MySQL:

• Using Create Table to Define a Unique MySQL Key
• Using Alter Table to Define a Unique MySQL Key

2A) Using Create Table to Define a Unique MySQL Key

• Step 1: You can use the following syntax if you want to define the Unique Key for a solitary column:

CREATE TABLE <table_name>
(
Column_name1 datatype() UNIQUE,
Column_name2 datatype(),
);

• Step 2: If you wish to define more than one Unique Key within a table you can use the following syntax:

CREATE TABLE <table_name>
(
Column_name1 datatype(),
Column_name2 datatype(),…
Column_namen datatype(),
UNIQUE (column_name1, column_name2)
);

• Step 3: You will be checking this with the help of an example now. Create a table as follows:

CREATE TABLE VATSA(  
     ID INT AUTO_INCREMENT PRIMARY KEY,  
     Company_name varchar(100) UNIQUE,  
     Address varchar(250) UNIQUE  
); 

• Step 4: Next, you need to insert some rows in this table as follows:

INSERT INTO VATSA VALUES (1, 'Vatsa_Empire', '132, ABC, Near Royal Club, BSR');  
INSERT INTO VATSA VALUES (2, 'Vatsa_Hotel', '138, ABC, Near Royal Club, BSR');

• Step 5: Now, if you try to fetch the values from this table, here’s the output you can expect:

• Step 6: Now, if you try to insert a new record with existing data, MySQL will throw an error for the following code snippet:

INSERT INTO VATSA VALUES (10, 'Vatsa_Empire', '204, ABC, Near Royal Club, BSR');  

2B) Using Alter Table to Define a Unique MySQL Key

• Step 1: MySQL allows you to add a Primary Key on the column of the existing table based on the syntax mentioned below:

ALTER TABLE <table_name>
ADD UNIQUE (column_name);

• Step 2: If you wish to apply the Unique Key constraint on a table called friend, you can use the following code snippet:

ALTER TABLE friend  
ADD CONSTRAINT uq_col  
UNIQUE (friend_name);

• Step 3: Finally, check the result with the help of the following code snippet:

DESCRIBE friend

3. What is a MySQL Candidate Key?

A characteristic or group of attributes that can uniquely identify a tuple is known as a candidate key in MySQL Keys.

The remaining properties, with the exception of the primary key, are considered candidate keys. Candidates have the same strength as the primary key.

Take a look at the “Employee” table as an example. Emp_Id, Emp_Number, and Emp_Name are the three attributes in this table. Emp_Id and Emp_Number will have unique values, however, Emp_Name may contain duplicate values because multiple employees may have the same name.

The candidate keys here are {Emp Id} and {Emp Number}.

4. What is a MySQL Foreign Key?

The Foreign Key in MySQL Keys allows MySQL to ensure referential integrity by placing constraints on data in the related tables. 

In MySQL, you can have more than one Foreign Key for a table where each Foreign Key references a Primary Key of various parent tables. 

However, once a Foreign Key constraint is in place, the Foreign Key columns from the child table need to have the corresponding row in the parent Key columns of the parent table. 

Or, the values in these Foreign Keys columns must be NULL for them to be valid.

Here are a couple of salient aspects of understanding MySQL Foreign Key:

• Prerequisites/Conditions to use a Foreign MySQL Key
• How to add a Foreign MySQL Key?
• How to Delete/Drop a Foreign MySQL Key?
• How to Disable Foreign MySQL Key Checks?

4A) Prerequisites/Conditions to use a Foreign MySQL Key

Here are a few conditions that a MySQL Foreign Key must conform to:

• The fields that need to be used in the Foreign Key relationship need to be indexed.
• Both the tables that are going to be linked need to be of the same database types.
• The Foreign Key field relationship needs to be similar in datatype as well for it to be valid.

4B) How to add a Foreign MySQL Key?

You can add a Foreign Key to an existing table by using the ALTER TABLE command along with an ADD FOREIGN KEY clause to describe the appropriate field as the Foreign Key as follows:

ALTER TABLE zoo  
ADD FOREIGN KEY  
(FK_species) REFERENCES species (id);

4C) How to Delete/Drop a Foreign MySQL Key?

• Step 1: If you wish to drop a Foreign Key constraint, you can leverage the ALTER TABLE statement as follows:

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

In the aforementioned syntax, you need to mention the name of the table from which you wish to drop the Foreign Key following the ALTER TABLE keywords. You also need to specify the constraint name after the DROP FOREIGN KEY keywords.

• Step 2: From the output, it is evident that the products table contains only one Foreign Key constraint, fk_category. You can use the following command to drop it from the table:

ALTER TABLE products 
DROP FOREIGN KEY fk_category;

• Step 3: To make sure that the Foreign Key constraint has been dropped, you can look at the structure of the products table as follows:

SHOW CREATE TABLE products;

4D) How to Disable Foreign MySQL Key Checks?

• Step 1: Quite often, you might find it advantageous to disable Foreign Key Checks. For instance, when you import data from a CSV File within a table. If you don’t disable Foreign Key checks, you would have to load data into proper order. This means that you would have to load data into the parent tables first followed by the child tables, which can be quite cumbersome.
• Step 2: Disabling Foreign Key checks allows you to load data into the tables in any given order. You can use the following statement for the same:

SET foreign_key_checks = 0;

• Step 3: You can enable it again by using the following code snippet:

SET foreign_key_checks = 0;

5. What is MySQL Super Key?

In a Relational Database, a Super Key is defined as a set of attributes that can identify every tuple of a relation. Since Super Key values are unique, tuples that have the same Super Key value should also have the same non-key attribute values.

For example, (EMPLOYEE ID, EMPLOYEE NAME) in the following EMPLOYEE database, two employees’ names can be the same, but their EMPLYEE ID cannot. As a result, this combination could be a crucial.

Frequently Asked Questions (FAQs)

What is a MySQL Key in a table?

A key, or index, as the title implies, allows you to access the tables. You can identify specific records and the links between tables if you know the key. Each key is made up of one or more fields, often known as field prefixes.

How to find Primary MySQL Keys?

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Your Database'
  AND TABLE_NAME = 'Your Table name'
  AND COLUMN_KEY = 'PRI';


SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Your Database'
  AND TABLE_NAME = 'Your Table name'
  AND COLUMN_KEY = 'UNI';

Can a Unique Key be NULL?

The primary key will not accept NULL values. A table can have only one primary key, but it can also contain numerous unique MySQL keys.

Can a Primary Key be NULL?

The set of columns that uniquely identify rows in a table is known as a primary key. When you create a primary key constraint, none of the columns that make up the primary key can have NULL constraints, which means they can’t contain NULL values. 

Before wrapping up, let’s cover some basics.

What is a Key in a Relational Database?

Within the confines of a Relational Database, a Key is defined as a column or a group of columns (attributes) leveraged to uniquely locate records in a table of a Relational Database.

Here is an example of a Key within a Relational Database:

You can use the Keys to extract or fetch rows from a table as and when required.

Relational Database Keys have various forms of constraint to conform to such as columns, which cannot hold duplicate values or null values.

The various keys in MySQL are:

• Primary Key
• Unique Key
• Candidate Key
• Foreign Key
• Super Key

Conclusion

You learned how to create a table to define and also how to alter the table for primary and unique keys. You also learned how to add and delete a foreign key in MySQL. It also gives a brief introduction to Relational Database Keys, how they are used to fetch rows and columns from a database when and as required and the features of MySQL before diving into the nitty-gritty of the topic. 

Extracting complex data from MySQL to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs to a Data Warehouse and visualize the transformed data in a BI tool. 

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable Hevo pricing that will help you choose the right plan for your business needs.

Did you like reading about MySQL keys? If yes, you will also like to get an understanding of MySQL Composite Primary Keys and the different methods of creating them.

Also, please don’t forget to share your thoughts about this blog in the comments below 😊

References:

• Dropping the primary key constraint