Primary Key in SQL: Everything You Need to Know

The Primary Key in SQL is a column that is used to identify a table uniquely. It performs multiple functions in an SQL table. The upcoming sections of this blog give you a guide on what a Primary Key is, how to add it, and how to remove it in SQL. Along with this, you will also learn the syntax and examples in depth. But before you move on to that, let us go through a brief description of what is Primary Key SQL.

What is Primary Key in SQL?

A Primary Key in a database table is a column or collection of columns uniquely identifying each table entry. It can be a single column or a Composite Key comprising two or more columns. 

The following are the uses of Primary Key:

• It uniquely identifies each row in the table.
• It creates connections between tables.
• It finds and accesses rows in the table quickly.
• It prevents the entry of duplicate data into the table.

The Primary Key query in SQL is used to retrieve the Primary Key of the table. It is classified into two types that is a single-column and a multi-column. You can get a better understanding of the subject by enrolling yourself in an online SQL Course.

Primary Key Syntax in SQL

The Primary Key constraint is used to construct a Primary Key. It has the following syntax:

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  [CONSTRAINT constraint_name] Primary Key (column1, column2, ...)
);

In the syntax mentioned above, table_name is the name of the table, column1 and column2 are the names of the columns making up the Primary Key and constraint_name is the optional name for the constraint.

Example

CREATE TABLE Employees (
  EmployeeID INT,
  EmployeeName VARCHAR(100),
  Salary DECIMAL(10, 2),
  Primary Key (EmployeeID)
);

How to Add Primary Key in SQL?

The steps mentioned below should be followed to add a Primary Key in SQL:

Step 1: Open the SQL database into which you wish to add the Primary Key.

Step 2: Enter the following command in the SQL editor:

ALTER TABLE table_name ADD CONSTRAINT pk_name Primary Key (column_name);

In the syntax mentioned above,

• table_name is the name of the table to which the Primary Key should be added.
• column_name indicates the name of the Primary Key column.
• pk_name is the Primary Key constraint’s name.

Step 3: Press enter to execute the command. This will add the Primary Key in SQL.

• Example 1 – An example to add a Primary Key to a table called ‘customers’:

ALTER TABLE customers ADD CONSTRAINT pk_customers Primary Key (customer_id);

The ‘customers’ table will now have a Primary Key constraint named ‘pk_customers’. The ‘customer_id’ column will be the Primary Key.

• Example 2 – To construct a table with a Primary Key, use the create TABLE statement. To do this, include the Primary Key term in the column definition. As an example:

 CREATE TABLE customers (
  customer_id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  PRIMARY KEY (customer_id)
);

Customers will be created as a table with three columns, that is, ‘customer_id’, ‘first_name’, and ‘last_name’. The Primary Key will be the ‘customer_id’ column.

How to Remove Primary Key in SQL?

You must follow the steps mentioned below to understand how to remove the Primary Key in SQL:

Step 1: Open the SQL database holding the table containing the Primary Key that you wish to delete.

Step 2: Enter the following command in the SQL editor:

ALTER TABLE table_name
DROP CONSTRAINT primary_key_name;

• Example – To delete the Primary Key from the products table, use the following command:

ALTER TABLE products
DROP CONSTRAINT product_id_pk;

Step 3: Click enter to run the command.

Alternate Method to Remove the Primary Key

Here are the following steps:

Step 1: Open SQL Server Management Studio.

Step 2: Expand the database that includes the table with the Primary Key you wish to delete in the Object Explorer.

Step 3: Select Design from the drop-down menu when you right-click the table.

Step 4: Remove the Primary Key by right-clicking the row in the Table Designer and selecting Remove Primary Key.

Step 5: Once done, you can save the table.

Advantages of Primary Key in SQL

The following are the advantages of the Primary Key in SQL:

• Unique Identification: A Primary Key guarantees that each row in a table has a unique value. This reduces data inaccuracies and makes it simpler to locate particular data.
• Improves Query Performance: Primary Keys can be used to generate indexes, and queries involving the column with the Primary Key can perform better.
• Enforces Data Integrity: By prohibiting duplicate entries in the Primary Key column, the Primary Key serves to ensure data integrity. This contributes to the accuracy and consistency of the data in the table.

Disadvantages of Primary Key in SQL

The following are the disadvantages to keep in mind:

• Complexity: Primary Keys might complicate the schema since they necessitate additional columns or column combinations.
• Update: If the Primary Key is a regularly changed column, updating data might be tough since you must guarantee that the new value is unique.
• Bottlenecks: The changes to the table can get slower if the Primary Key column is indexed since the database must update the index every time the Primary Key is updated.

Conclusion

Primary Key SQL is a column or a group of columns that are used to uniquely identify each row in the table. You can start using it once you have a good understanding of the subject and know what you are working with.