Managing Primary Keys in MySQL

What is a Primary Key?

A primary key constraint designates a column as the primary key of a table or view. A composite primary key designates a combination of columns as the primary key. A primary key combines a Not Null and unique constraint in one declaration. Therefore, to satisfy a primary key constraint, no primary key value can appear in more than one row in the table, and no column that is part of the primary key can contain Null. A table or view can have only one primary key.

Creating Primary Keys

1. Open a table and select New Primary Key on the Table menu.
2. Add required columns to the key and click OK. You can also switch to the Constraints tab and create the primary key within the shortcut menu.

Creating a table with a Primary Key

Typically, a primary key for a table is defined in the CREATE TABLE statement. Below is the MySQL syntax for creating a table with a primary key:

CREATE TABLE table_name
(
  column1 column_definition,
  column2 column_definition,
  ...

  CONSTRAINT [constraint_name] 
   PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)
);

Editing Primary Keys

1. Navigate to the key in Database Explorer and double-click it or open the table that owns the primary key.
2. Switch to the Constraints tab and choose Edit Constraint on the shortcut menu.

Adding Primary Keys to the existing tables

You can add a primary key to a table with the help of the ALTER TABLE statement. Below is the MySQL syntax for adding a primary key to the existing table:

ALTER TABLE table_name
  ADD CONSTRAINT [ constraint_name ]
    PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)

Dropping Primary Keys

1. Navigate to the key in Database Explorer.

2. Select Delete on the shortcut menu.

   -or-

3. Open the table that owns the primary key.
4. Switch to the Constraints tab.
5. Select Delete Constraint on the key’s shortcut menu.

Manipulating Primary Keys using Visual Database Diagram

dbForge Studio for MySQL allows managing MySQL and MariaDB primary keys as well as foreign keys in a comprehensive and convenient GUI.

To add, delete, or alter a MySQL primary key on a table:

1. On a diagram, right-click the table to which you want to add a primary or foreign key.

2. Select an operation you want to perform from the context menu that appears.

3. You can add, drop, or change primary or foreign keys settings in the Configuration window that opens.

Want to Find out More?

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for MySQL.

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.

Ready to start using dbForge Studio for MySQL?

Download a free 30-day trial

• Previous
• Next