Primary key vs unique key

What is the difference between primary and unique keys?

What primary keys are and what they are used for is explained in the article describing the difference between primary and foreign keys.

A short summary: Primary keys serve as the identifier of a unique record, it can be unique information, such as a driver’s license number, telephone number including area code, car VIN number, etc.

A primary key can contain one or more columns and there can be only one primary key in a database table, even if it is composed of multiple columns.

Quite often a primary key is a unique number or identifier generated when the record is stored in the database table.

Unique keys

In certain scenarios, you may need to ensure that data ​​stored in a column or group of columns are unique in a database table. For example email address or usernames.

In Luna Modeler, you can define a new unique key (alternate key) and add columns to the key. The following example shows a new key for the email table column.

Generated SQL script (MySQL):

CREATE TABLE `ecommerce`.`customer` ( 
   `id` INT AUTO_INCREMENT,
   `name` VARCHAR(150) NOT NULL,
   `surname` VARCHAR(150) NOT NULL,
   `email` VARCHAR(150),
   `driving_license` VARCHAR(100),
   `phone` VARCHAR(200),
   `born` DATE,
    PRIMARY KEY `Primary key`(
   `id`
    )
)
ENGINE = InnoDB;

ALTER TABLE `ecommerce`.`customer` ADD CONSTRAINT unique_email UNIQUE (
   `email`
);

To maintain the uniqueness of data stored in multiple columns, you can create a key and add more than one column to the alternate key. In the following example, you can see a unique key made of columns name, surname and born.

Generated code (MySQL):

ALTER TABLE `ecommerce`.`customer` ADD CONSTRAINT ak_customer UNIQUE (
   `name`,
   `surname`,
   `born`
);

The key benefit of unique keys becomes obvious when you try to insert a new record into the database table. The second record defined with exactly the same values will not be stored in the table, the INSERT statement execution will result in an error.

Relationships and unique keys

Unique keys can be used for relationships. The following paragraph describes how to model such scenario visually.

In Luna Modeler, the primary key is always used for new relationships defined between parent and child table.

ALTER TABLE `ecommerce`.`customer_comment` 
  ADD CONSTRAINT `customer-customer_comment`
  FOREIGN KEY ( 
   `customer_id`
)   REFERENCES `ecommerce`.`customer`( 
   `id`
) ;

If you want to use the unique key instead of the primary key, create a new relationship, select it and expand the section Key. Choose the unique key from the dropdown:

Generated code (MySQL):

ALTER TABLE `ecommerce`.`customer_comment` 
  ADD CONSTRAINT `customer-customer_comment`
  FOREIGN KEY ( 
   `customer_name`, 
   `customer_surname`, 
   `customer_born`
)   REFERENCES `ecommerce`.`customer`( 
   `name`, 
   `surname`, 
   `born`
) ;

Primary foreign keys

The example above shows a table with both the primary key (id) and three columns that belong to the foreign key (customer_name, customer_surname and customer_born).

You may want to make additional changes and e.g. remove the id column and use the three columns as a primary key in the child table customer_comment. In Luna Modeler the result would look like this:

Generated code:

CREATE TABLE `ecommerce`.`customer_comment` ( 
   `customer_name` VARCHAR(150),
   `customer_surname` VARCHAR(150),
   `customer_born` DATE,
   `comment` TEXT,
   `date_added` TIMESTAMP,
    PRIMARY KEY `Primary key`(
   `customer_name`,
   `customer_surname`,
   `customer_born`
    )
)
ENGINE = InnoDB;

ALTER TABLE `ecommerce`.`customer_comment` 
  ADD CONSTRAINT `customer-customer_comment`
  FOREIGN KEY ( 
   `customer_name`, 
   `customer_surname`, 
   `customer_born`
)   REFERENCES `ecommerce`.`customer`( 
   `name`, 
   `surname`, 
   `born`
) ;

The benefit of primary foreign keys is that you may not need any other identifier than the set of columns assigned to the foreign key.

Note: this article contains examples crated for MySQL projects in Luna Modeler. Very similar database design can be done in projects for PostgreSQL or MariaDB.

Thanks for reading!