SQL Primary Key | What is Primary Key in SQL With Example?

Summary: You will learn in this article What is the Primary Key in SQL with example. If you are a developer or want to learn databases or if you’ve already worked with databases, then you could hardly miss the term – Primary Key (PK).

What is Primary Key in SQL? Primary keys are played important role in SQL databases. This key provides a unique ID for every row in a database table. you must know what primary keys are, how to create them, and what data values are allowed in them. Read this article to learn SQL Primary Key in detail.

What is Primary Key in SQL

A primary key is a field in a table, it uniquely identifies every row/data in a database table. Note:- Primary keys must contain unique values. A SQL primary key column can’t have NULL values.

A table can have only 1 (one) primary key, it may consist of single or multiple fields. furthermore, when multiple fields are used as a primary key in SQL, then they are called composite keys. Furthermore, if a table has a primary key defined on any field(s), then you can’t have 2 (two) records having the equivalent value of that field(s).

Note: You’d use these concepts or ideas while creating database tables.
The simplified definition of a PK:
“SQL Primary key (PK) is a value it will be unique for every record/data in the table.”
And PK Rule – “Every/each row in a database table should have a Primary Key (pk) defined.”

What is Primary Key

How To Create Primary Key in SQL

The given below syntax to define the ID attribute as a SQL primary key in a CUSTOMERS table.

CREATE TABLE CUSTOMERS(
   Customer_Id   INT           NOT NULL,
   Customer_Name VARCHAR (30)  NOT NULL,
   Customer_Age  INT           NOT NULL,
   Customer_Address  CHAR (50) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

CREATE TABLE CUSTOMERS(

Customer_Id INT NOT NULL,

Customer_Name VARCHAR (30) NOT NULL,

Customer_Age INT NOT NULL,

Customer_Address CHAR (50) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

To create a PK constraint on the “Customer_Id” column when the CUSTOMERS table already exists, then use the following SQL syntax as given below −

ALTER TABLE CUSTOMER ADD PRIMARY KEY (Customer_Id);

1	ALTER TABLE CUSTOMER ADD PRIMARY KEY (Customer_Id);

Furthermore, if you utilize the ALTER TABLE statement to add a primary key (pk), the PK column(s) should have already been declared to not contain NULL values (when the table was 1st created).
For defining a PK constraint on multiple columns, then use the SQL syntax as given below.

CREATE TABLE CUSTOMERS(
   Customer_Id   INT           NOT NULL,
   Customer_Name VARCHAR (30)  NOT NULL,
   Customer_Age  INT           NOT NULL,
   Customer_Address  CHAR (50) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

CREATE TABLE CUSTOMERS(

Customer_Id INT NOT NULL,

Customer_Name VARCHAR (30) NOT NULL,

Customer_Age INT NOT NULL,

Customer_Address CHAR (50) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

Therefore, To create a PRIMARY KEY constraint on the "Customer_Id" and "Customer_Name" columns when the CUSTOMERS table already exists, then use the following SQL statement.

ALTER TABLE CUSTOMERS 
   ADD CONSTRAINT PK_CUSTID PRIMARY KEY (Customer_Id, Customer_Name);

1 2	ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (Customer_Id, Customer_Name);

How To Delete/Drop Primary Key In SQL

You can clear the primary key (pk) constraints from the table then use the syntax given below.

ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;

1	ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;

What is Non-Numeric Primary Keys?

In the above examples, you can see the only numeric primary keys. However, it is possible that we can use the primary keys of other data types.

For example, let’s suppose we have a table that stores all the countries in the world. Some of the columns are country_name, country_ISO3_code, and Capital_city. The country ISO code and Country ISO3 code is a unique code assigned to each country name. We can use this non-numeric code in the given below example as the primary key.

Country_Name	Country_ISO3_Code	Capital_City
Afghanistan	AFG	Kabul
Australia	AUS	Canberra
Canada	CAN	Ottawa
France	FRA	Paris
India	IND	New Delhi

The given below following SQL statement creates the table country with a primary key in the column country_ISO3_Code:

CREATE TABLE country (
    country_ISO3_Code  char(3) PRIMARY KEY,
    country_name       varchar(40),
    capital_city       varchar(40),
);

CREATE TABLE country (

country_ISO3_Code char(3) PRIMARY KEY,

country_name varchar(40),

capital_city varchar(40),

);

Important Note: We use a char(3) data type for the country_ISO3_code to store the three-letter code.

What Are the Properties of a Primary Key?

There are the properties of each primary key column or column such as:

A table can accept only one primary key
It enforces uniqueness, they aren’t accepting any duplicate values
A primary key column can’t accept null values
A primary key (pk) uniquely identifies each field or rows
It can be created at a table or column level, using CREATE TABLE statement or ALTER TABLE statements
Primary columns have the most or maximum length of 900 bytes
A single-column primary key is a straightforward or simple one. The one consisting of multiple columns is known as a composite primary key

Primary key VS. UNIQUE (alternate keys)

When I’m designing a database, I continuously persist with some rules concerning PKs and unique values. this can be the simplest I’ve learned (so far) and besides technical reasons, it’s also worth mentioning that using this approach you’ll keep consistent throughout the complete database model. So, these rules are:

Each table in the database should have the primary key (PK) defined. This will not only improve the overall database performance but is additionally essential in order that data are related/connected and consistent
As you can see that in each table, I’ll add the column named id. It will be utilized as a PK column, the type is an unsigned integer, with IDENTITY set to (1,1). This way, the Database Management System (DBMS) will automatically generate PK values as we add rows. Furthermore, using integers as PKs also considerably improves the performance (index is created over that attribute automatically)
All attributes, besides the primary key in sql, which contain unique values, should be defined as UNIQUE (alternate keys). Therefore, this property could be defined on a single attribute, or on a combination of many attributes. it will help to prevent inserting unwanted duplicated values.

Conclusion:

In this article, you learned everything concerning the SQL primary key. you’ve got competent the way to create it in different databases at different levels using both CREATE TABLE & ALTER TABLE commands. This article also explored how to drop a primary key (PK) in multiple databases. The primary key is a necessary constraint that will assist you to maintain the integrity and authenticity of databases.

Understanding the primary key and once it’s used is the backbone of the database theory. The next thing is understanding the concepts of a foreign key and how foreign keys are used to relate data. I hope you will enjoy it!