What is Primary key in SQL? How to choose Primary key in table? Example

If you've ever dealt with databases, you've almost certainly come across the term - primary key (PK). And if you're following this series to learn about databases, this piece should help you understand what the PK is all about. Even if you're a database specialist, you could learn something new or improve your skills. So sit back, relax, and let's get started with the PKs.

What is a Primary key?

In the database world, we're actually surrounded by PKs. We, on the other hand, tend to take them for granted. Let's start with a simple description of a PK before moving on to examples:

"In a table, a primary key is a value that is unique for each record."

"Each table in the database should have a PK specified," says the regulation.

Each entry in a table is uniquely identified by the PRIMARY KEY constraint. Primary keys cannot have NULL values and must have UNIQUE values. A table can only have ONE primary key, and this primary key can be made up of one or more columns (fields).

Understanding PK with a database structure

We can nearly always anticipate having one table in our database for each real-world entity. We'll have one table for nations, one for cities, one for staff, one for calls, and so on.

Now it's time to look at some real-life instances of primary keys:

• Every country on the planet has its own name and code (ISO, ICAO, IOC, E. 164 or any other). In actual life, both are PKs (and in the database, they might be PKs or alternative keys/unique values).
• Each employee has their own personal number, corporate code, and other identifiers.
• Each call contains a one-of-a-kind combination of who called whom and when. (Please note that we require all three of them; merely two would suffice.)

We'll wrap off this section with a more detailed definition.

"A primary key is a single value, or a set of values from the database, that uniquely identifies each record in the table." We can simply discover the relevant record and retrieve the remaining values from that record if we know this value/combination."

If you know the nation's name, for example, you can quickly locate the row with that name and obtain the remaining numbers relating to that country - population, statistical data, and so on.

The real purpose of the primary key

Databases would not function as they do now without PKs, or, to be more exact, would not function at all.

Let's create a table named the city with the below query : 

CREATE TABLE city (
    id int  NOT NULL IDENTITY(1, 1),
    city_name char(128)  NOT NULL,
    lat decimal(9,6)  NOT NULL,
    long decimal(9,6)  NOT NULL,
    country_id int  NOT NULL,
    CONSTRAINT city_pk PRIMARY KEY  (id)
); 

As you can observe, we have a primary key associated with column 'id'. we have associated a constraint city_pk with it to specify it more precisely.

Let's create another table country with the below query : 

CREATE TABLE country (
    id int  NOT NULL IDENTITY(1, 1),
    country_name char(128)  NOT NULL,
    country_name_eng char(128)  NOT NULL,
    country_code char(8)  NOT NULL,
    CONSTRAINT country_ak_1 UNIQUE (country_name),
    CONSTRAINT country_ak_2 UNIQUE (country_name_eng),
    CONSTRAINT country_ak_3 UNIQUE (country_code),
    CONSTRAINT country_pk PRIMARY KEY  (id)
);

Databases are used to store (often) vast volumes of data. We created rows in our tables in the last post. That's OK, but increasing rows is pointless if we can't do anything with them

We'll need the means to recognize each individual row using known values if we're going to achieve that (s). I'd divide these values into two categories:


In the actual world, we employ these values. We can search our database for 1 or more entries using these variables. In the example of the nation table, if we know the country's name, we can quickly locate a record in our database that is relevant to that country and access all additional values from that record. 

If we input the country's actual name, the unique value will be used, and the result will be precisely one row. As you can see, this is a really elegant method of obtaining the information we want.

When we input search parameters on the front-end form and then transmit that value to the SQL query, we use this method.

Values that have no real-world application (usually primary keys). It works in the same way as the previous scenario, only we'll utilize the id column's integer value to access a specific nation. I'm sure you can see the flaw in this method.


We can't predict which id value will be allocated to which nation since PKs were produced by the system; they'll be assigned in the same order countries were entered in the table, which is pretty much a "random" order.

Primary keys Vs Unique keys

A primary key is a table column that uniquely identifies each tuple (row) within it. The table's integrity restrictions are enforced by the primary key. In a table, only one primary key can be used. Duplicate and NULL values are not accepted in the main key.

The primary key value in a table changes relatively seldom, thus it is carefully packed in cases where changes are likely to occur infrequently. A foreign key of one table can relate to a primary key of another table.

In relation to or table, unique key constraints also identify an individual tuple. Unlike primary keys, a table can contain several unique keys.

Only one NULL value per column can be accepted under unique key restrictions. The foreign key of another table also refers to unique restrictions. It's useful when you want to impose unique restrictions on a column or a set of columns that aren't main keys.

That's all about what a primary key is and when it's utilized. The next step is to comprehend the notion of a foreign key and how it is used to link data. We'll go over this in more detail in a later article. With these two in your pocket, you'll be able to get started building databases and expand your expertise as you go. There's still a lot more to learn to become a database expert, but you've already taken the initial steps in that direction.