Primary Key In SQL : Everything You Need To Know About Primary Key Operations

In an era where we generate 2.5 quintillion bytes of data every day, it is very important to handle data in a proper manner and identify unique records. So, in this article on Primary Key in SQL, I will discuss how each record in a table can be uniquely identified when there are relational databases present.

The following topics will be covered in this article:

1. What is a Primary Key?
2. Rules for Primary Key
3. Primary Key Operations:
   • Primary Key on Create Table
   • Primary Key on Alter Table
   • Drop Primary Key

What is a Primary Key in SQL?

Primary Key Constraint is a type of key through which you can uniquely identify every tuple or a record in a table. Every table can have only one primary key but can have multiple candidate keys. Also, each primary key should be unique and must not contain any NULL values.

Primary keys are used along with the foreign keys to refer to various tables and form referential integrities. For Table A, a primary key can consist of single or multiple columns.

Now that you know what is the primary key, next in this article on Primary Key in SQL, let us understand the rules of the primary key.

Rules for Primary Key

The rules of Primary Key are as follows:

1. All the values in the column chosen as the primary key must be unique.
2. Each and every table can have only one primary key
3. No value in the primary key column can be NULL
4. You cannot insert a new row with a pre-existing primary key

Now that you know what are the rules of a primary key, next in this article on Primary Key in SQL, let us see the operations of the primary key.

Primary Key Operations:

To understand the various operations present on the primary key, consider the following table:

Customers Table:

Primary Key on Create Table

You can use the following syntax to create a primary key on the “customerID” column while you are creating this table:

#For SQL Server/ MS Access/ Oracle
CREATE TABLE Customers (
CustomerID int NOT NULL PRIMARY KEY,
CustomerName varchar(255) NOT NULL,
PhoneNumber int
);
#MySQL
CREATE TABLE Customers (
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
PhoneNumber int
PRIMARY KEY (customerID)
);

Apply Primary Key on Multiple Columns

To apply primary key on multiple columns while creating a table, refer to the following example:

CREATE TABLE Customers (
customerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
PhoneNumber int,
CONSTRAINT PK_Customer PRIMARY KEY (CustomerID,CustomerName)
);

Refer to the below image.

Next, in this article on Primary Key in SQL, let us see how to use the primary key on Alter Table.

Primary Key on Alter Table

You can use the following syntax to create a primary key on the “customerID” column when the “customers” table is already created and you just want to alter the table:

ALTER TABLE Customers
ADD PRIMARY KEY (CustomerID);

If you want to add a name to the Primary Key constraint and define it on multiple columns, use the following SQL syntax:

ALTER TABLE Customers
ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerID,CustomerName);

Next, in this article on Primary Key in SQL, let us understand how to drop a primary key

Delete/ Drop Primary Key

To drop the primary key, you can refer to the following example:

#For SQL Server/ MS Access/ Oracle
ALTER TABLE Customers
DROP CONSTRAINT PK_Customer;
#For MySQL
ALTER TABLE Customers
DROP PRIMARY KEY;

With this, we come to an end to this article. I hope you understood how to use the Primary Key in SQL. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article on “Primary Key in SQL” and I will get back to you.