Data modeling for PostgreSQL | Archive

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 Moon 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.

Image of alternate key. Generated SQL will contain a new unique constraint.

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.

Alternate key or unique key if you will. Generated code will contain new unique constraint.

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 Moon Modeler, the primary key is always used for new relationships defined between parent and child table.

Primary key used for relationship in Moon Modeler

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:

Alternate key used for relationships in Moon Modeler

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 Moon 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 Moon Modeler. Very similar database design can be done in projects for PostgreSQL or MariaDB.

Thanks for reading!

What is the difference between primary and foreign keys in relational databases?

Primary keys serve as the identifier of a unique record in a database table. It can be unique information, such as a driver’s license number, telephone number including area code, car VIN number, etc.

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

Multi-column primary keys

A primary key can contain one or more columns. There can be only one primary key in a table, even if it is composed of multiple columns. Example of a primary key that contains multiple columns:

Unique identifiers, IDs and auto-increment

In many cases, a primary key is a unique number that is either created outside the database system and stored in the database or is generated directly when the record is stored in the database table. This is done, for example, by the AUTO_INCREMENT column property in MySQL or MariaDB databases.

Foreign keys

A foreign key is defined by a column or more columns that point to the primary key of another table. There can be multiple foreign keys in a table, depending on the relationship that exists between the tables. In relational databases, a foreign key can be defined using a foreign key constraint, which guarantees data integrity between the child and the parent tables. By setting a foreign key constraint, you can ensure that the data in a given column in a child table matches the records in a column in the parent table.

The following example shows the contact_id column in the table Customer. The generated SQL script shows the foreign key definition and the reference to the id column in table Contact.

Example of a structure that allows storing records for customers, more than one email or phone number per customer and storing more than one address per contact.

An alternate design might be similar to the one displayed on the following screenshot:

JSON structure and embedded records

Another way to store data in cases similar to the example mentioned above is to store everything in a column that is of data type JSON. It will not be necessary to store data in other tables and create foreign keys. Whether or not this is the right way depends on many factors, and it is not possible to say whether one approach is always more correct than the other.

Visualization of JSON structures

In terms of visualization, what can be stored in a JSON structure is often complex. However, in Moon Modeler you can also visualize JSON structures and you can have a perfect overview of how data is stored. You can easily see the JSON structure, whether the values are stored in arrays, as objects, etc.