What is a Primary Key?

Primary Key Meaning

In the context of a database, a primary key is simply a unique identifier for a row. However, the concept is critical to an efficient database, since it is any field, or combination of fields that you can guarantee will be unique for each record in a relational database table. The amount of data and values that a table holds can reach the hundreds of thousands for some companies, so it's essential that these identifiers are unique.

An example of a primary key would be in the case of our "customers" table. The customer_id field is unique for each customer, therefore it can be used as a primary key.

In a database, every table needs to have a primary key column, and there is no limit to the number of tables in a database that can use the same field from another table as their primary key.

The most common type of data used in a primary key field is an integer, but it can also be text, binary or a date. A common convention is to use an identity column to generate values automatically.

What is the purpose of a primary key in a table?

The primary key is a unique identifier for rows in a table, so that no two records in the table have the same value. This means that if you have a User table, all users will have a different UserId.

A primary key uniquely identifies each row. It does not allow NULLS and must be unique. This is because the primary key does not allow nulls, since it would be impossible to be sure of the uniqueness of null values.

The primary key can consist of one or more columns, since you can use a concatenation of values as an identifier, just as long as they are all unique amongst themselves.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

SQL Primary Key

Constraints with SQL Primary Keys: as mentioned above, the primary key must contain unique values, and none of them can contain "null" or "blank" values.

The command below creates a PRIMARY KEY on the "ID column" when, for example, the "person" table is created (courtesy of https://www.w3schools.com/)

Examples

A primary key is a unique identifier that is used to identify each row in a table. Here are some examples of primary keys:

1. Employee ID: In an employee table, the primary key could be the unique employee ID assigned to each employee.
2. Student ID: In a student table, the primary key could be the unique student ID assigned to each student.
3. Order ID: In an order table, the primary key could be the unique order ID assigned to each order.
4. Customer ID: In a customer table, the primary key could be the unique customer ID assigned to each customer.
5. Product ID: In a product table, the primary key could be the unique product ID assigned to each product.
6. Transaction ID: In a transaction table, the primary key could be the unique transaction ID assigned to each transaction.
7. Location ID: In a location table, the primary key could be the unique location ID assigned to each location.
8. Invoice ID: In an invoice table, the primary key could be the unique invoice ID assigned to each invoice.
9. Event ID: In an event table, the primary key could be the unique event ID assigned to each event.
10. Account Number: In an account table, the primary key could be the unique account number assigned to each account.

Learn more with Secoda

Secoda is the easiest way to search your data. Imagine using ChatGPT for your metadata - Secoda AI lets you or anyone on your team, regardless of technical ability, get a contextual answer to answer any question.

‍