Following the best practices of RDBMS involves the consistent usage of Primary Keys when designing databases to create and enforce Referential Integrity which is a major requirement for Data Quality and proper relational database design. In this tutorial, we provide background information on Primary Keys and their function as well as give code examples to illustrate the theory.
What is a Primary Key in MySQL?
Primary Key (PK) comprises a column or a combination of columns whose role is to provide a unique identifier to each record in a table. The main points behind Primary Keys are the following:
– A table can have only one Primary Key but it can be a Composite Key consisting of multiple columns.
– Since it uniquely identifies each row in the table, a primary key column cannot have NULL values.
– A primary key column must contain unique values. If a Primary Key is composite, the combination of values in the primary keys columns must be unique.
– Integer datatype is the best choice for primary key columns because MySQL works faster with integers.
Why do you need a Primary Key?
Primary Keys are a cornerstone of database normalization. The main reason why we need Primary Keys is to ensure data integrity and prevent data redundancy in a database. The main purpose of a Primary Key is to implement a relationship between two tables in a database. Relational databases must have PKs to relate a parent table to a child table since the parent table Primary Key is migrated to the child table as its Foreign Key (FK). If a table in your database doesn’t relate to any other table, it doesn’t need a primary key.
The absence of Primary Keys on a MySQL or MariaDB database indicates a very poor database design since it leads to:
– inferior performance
– renaming data columns affects other tables
– replication failures, etc.
Difference between Primary Key and Unique Key
Both Unique and Primary Keys guarantee the uniqueness of a column or a set of the columns on which they are defined. There is an automatically defined Unique Key constraint within a Primary Key constraint. However, a table can have several Unique constraints, but only one Primary Key constraint. Unlike Primary Keys, Unique Keys accept NULL values (one per table).
Unique Keys assure that the value in the column will be unique throughout the table. In case you try to add a duplicate record, you will get an error. On the screenshot below, you can see the error notification we got in dbForge Studio for MySQL after we tried to add a duplicate record to a table.
Difference between Primary Key and Foreign Key
A Foreign Key comprises a column or a set of columns in a database table that serves to link two tables. Most often, a Foreign Key column in the child table is Primary Key in the parent table. Unlike Primary and Unique Keys, MySQL Foreign Keys can accept multiple NULL values. You can have more than one Foreign Key in a table. Another important difference to consider is the fact that Foreign Keys do not automatically create an index, clustered or non-clustered. You must manually create an index on foreign keys.
In the screenshot below, the table actor has a Primary Key column actor_id, the table film in its turn has a Primary Key column film_id. The columns actor_id and film_id form Foreign Keys for the film_actor table.
The screenshot represents the Database Design functionality of dbForge Studio for MySQL that helps painlessly perform various database tasks from the database structure visualization to Primary and Foreign Keys configuration.
How to create a table in MySQL with a Primary Key
Most often, the primary key for a table is defined in the CREATE TABLE statement. MySQL syntax for creating a table with a primary key is the following:
CREATE TABLE table_name
(
column1 column_definition,
column2 column_definition,
...
CONSTRAINT [constraint_name]
PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)
);The screenshot below demonstrates a worked example of creating a table with a Primary Key in MySQL with the help of dbForge Studio for MySQL. Among other useful features, the IDE allows obtaining quick object information and syntax check instantly.
How to add a Primary Key to an existing table in MySQL
You can add a primary key to a table using the ALTER TABLE statement. MySQL syntax for adding a primary key to an existing table is the following:
ALTER TABLE table_name
ADD CONSTRAINT [ constraint_name ]
PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)The screenshot below demonstrates a worked example of adding a Primary Key to the existing table in MySQL with the help of dbForge Studio for MySQL. Its Code Completion functionality eliminates errors and allows writing SQL code quickly and effectively.
How to drop a Primary Key in MySQL
To remove Primary Key from a table, you can use the ALTER TABLE statement. The syntax to delete a primary key from a table is the following:
ALTER TABLE table_name
DROP PRIMARY KEY;See the screenshot for the worked example of dropping a Primary Key from a MySQL table with the help of dbForge Studio for MySQL. Its smart word and phrase completion help code faster and more accurately.
Managing Primary Keys using Visual Database Diagram
dbForge Studio for MySQL allows manipulating MySQL and MariaDB primary keys as well as foreign keys in a convenient interface without writing a single line of code. To add, delete, or change a MySQL primary key on a table:
1. Right-click the table on a diagram you want to add a primary or foreign key to.
2. From the context menu that appears, select an operation you want to perform.
3. In the Configuration window that opens, you can add, remove, or change primary or foreign keys settings.
Conclusion
Can you create a database table without a primary key? Yes, in MySQL, it is not required for every table to have a primary key.
Should you create a database table without a primary key? No, definitely not.
dbForge Studio for MySQL, being a universal GUI tool for MySQL and MariaDB database development, management, and administration, significantly facilitates dealing with primary key constraints.
