The SQL Primary Key: Benefits, Properties and Uses (2024)

What Are the Benefits of a Primary Key in SQL?

The most significant advantages of a primary key are:

• It uniquely identifies each row of a table
• It gets a unique index for each primary key column that helps with faster access

What Are the Properties and Rules of an SQL Primary Key?

The properties of each primary key column or columns are:

• It enforces uniqueness by not accepting any duplicate values
• A primary key uniquely identifies each field
• A table can only take one primary key
• Primary columns have a maximum length of 900 bytes
• A primary key column cannot accept null values
• A single-column primary key is a simple one. The one consisting of multiple columns is called a composite primary key
• It can be created at a column or table level, using CREATE TABLE or ALTER TABLE statements

How to Create an SQL Primary Key?

There are two ways to create a primary key column; through coding, or the GUI of the SQL Server Management Studio. You will go through both ways in this article, beginning with the Management Studio method.

Creating a Primary Key in SQL using SQL Server Management Studio

You can create a primary key column using the SQL Server Management Studio GUI with the following steps.

1. Open the database and right-click on the table name where you want to set the primary key.

2. Next, right-click on the column name and select the set primary key option.

3. This will make a change, and a key will appear beside the column, confirming that the column is now a primary key column.

Creating a Primary Key in SQL Using Code

You can create a primary key column in a table using the primary key constraint.

CREATE TABLE Students(

ID INT PRIMARY KEY,

Name VARCHAR(20),

Email_ID NVARCHAR(25)

);

INSERT INTO Students VALUES (1, 'George', 'ge.com');

INSERT INTO Students VALUES (2, 'David', 'da.com');

INSERT INTO Students VALUES (3, 'Aakash', 'aa.com');

SELECT * FROM Students;

Output:

Now, try inserting a duplicate value to check if the primary key constraint is applied or not.

INSERT INTO Students VALUES (2, 'Chris', 'ch.com');

SELECT * FROM Students;

Output:

As you can see in the output, it did not complete the last INSERT INTO command as the ID value 2 was duplicate.

Using a SQL Primary Key on Create Table

As mentioned earlier, you can create a primary key using CREATE TABLE and ALTER TABLE commands. You can also use it to create the primary key at both column and table levels. This section will look at how to use the CREATE TABLE command to create column and table level primary keys.

Example: Using Primary Key at the Column Level in SQL on Create Table

Column level primary key means defining it only for a single column in the entire table. Using the same Student table reference with the same values, create the primary key at column level and checks if it works as expected.

MySQL

For defining a primary key at column level in MySQL, use the following code:

CREATE TABLE Students(

ID INT,

Name VARCHAR(20),

Email_ID NVARCHAR(25),

PRIMARY KEY(ID)

);

INSERT INTO Students VALUES (1, 'George', 'ge.com');

INSERT INTO Students VALUES (2, 'David', 'da.com');

INSERT INTO Students VALUES (3, 'Aakash', 'aa.com');

SELECT * FROM Students;

Output:

Now, you need to check if it’s implemented properly.

INSERT INTO Students VALUES (2, 'Chris', 'ch.com');

SELECT * FROM Students;

Output:

SQL Server/Oracle/MS Access

For defining a primary key at column level in SQL Server/Oracle/MS Access, use the following code:

CREATE TABLE Students(

ID INT PRIMARY KEY,

Name VARCHAR(20),

Email_ID NVARCHAR(25)

);

INSERT INTO Students VALUES (1, 'George', 'ge.com');

INSERT INTO Students VALUES (2, 'David', 'da.com');

INSERT INTO Students VALUES (3, 'Aakash', 'aa.com');

SELECT * FROM Students;

Output:

INSERT INTO Students VALUES (2, 'Chris', 'ch.com');

SELECT * FROM Students;

Output:

Example: Using Primary Key at Table Level in SQL on Create Table

Table level primary key is helpful if you want to define multiple columns as primary keys in a single table. It is also referred to as a composite primary key. For this example, you have to define the Email_ID column as the primary key and the ID column. The syntax to define it for MySQL, SQL Server, Oracle, and MS Access is:

CREATE TABLE Students(

ID INT,

Name VARCHAR(20),

Email_ID NVARCHAR(25),

CONSTRAINT PK_Students PRIMARY KEY (ID, Email_ID)

);

INSERT INTO Students VALUES (1, 'George', 'ge.com');

INSERT INTO Students VALUES (2, 'David', 'da.com');

INSERT INTO Students VALUES (3, 'Aakash', 'aa.com');

SELECT * FROM Students;

Output:

-- checking the implementation of the primary key

INSERT INTO Students VALUES (2, 'Chris', 'ch.com');

SELECT * FROM Students;

Output:

As you can see in the output, the table still accepts the entry. Why do you think that is? That’s because the data in the Email_ID column was still different. Thus, for the system, it was two different rows with different values. Now, try to enter a row with the same values for ID and Email_ID and see what happens.

INSERT INTO Students VALUES (2, 'Chris', 'da.com');

SELECT * FROM Students;

Output:

As expected, the system threw an error.

Using SQL Primary Key on Alter Table

The ALTER TABLE command can create a primary key on the column of an already created table. You can yet again define a single column or multiple columns using the ALTER TABLE command. One thing to note while using the ALTER TABLE command is that while using it, you need to ensure that all the rows already inserted into the column should be unique. It is also important to ensure that the columns must be defined with a NOT NULL constraint; otherwise, the query will fail and throw an error.

Example: Using Primary Key in SQL on Alter Table for Single Column

You can use the ALTER TABLE command with MySQL, SQL Server, Oracle, and MS Access. For this example, you will have to use the Oracle server. First, create the Students table defining no column as the primary key to describe it using the ALTER TABLE command.

CREATE TABLE Students(

S_ID NUMBER NOT NULL,

Name VARCHAR2(20),

Email_ID VARCHAR2(25)

);

INSERT INTO Students VALUES (1, 'George', 'ge.com');

INSERT INTO Students VALUES (2, 'David', 'da.com');

INSERT INTO Students VALUES (3, 'Aakash', 'aa.com');

SELECT * FROM Students;

Output:

Now, use the ALTER TABLE command to make the ID column primary and insert a duplicate value.

ALTER TABLE Students

ADD PRIMARY KEY (S_ID);

INSERT INTO Students VALUES (2, 'Chris', 'ch.com');

Output:

As expected, the server did not accept the row because of the primary key infringement.

Example: Using Primary Key in SQL on the Alter Table for Multiple Columns

Again, the syntax is the same for MySQL, SQL Server, Oracle, and MS Access. Here, use the Oracle server and use the ALTER TABLE command to name and create a primary key for the S_ID and Email_ID. Use the same code as above to create the table, and then add the following ALTER TABLE command and insert duplicate values.

ALTER TABLE Students

ADD CONSTRAINT PK_Students PRIMARY KEY (S_ID, Email_ID);

Output:

-- Adding duplicate value

INSERT INTO Students VALUES (2, 'Chris', 'da.com');

Output:

You got the error as you were supposed to. Remember, the server will consider it the same only if the values of both the columns are the same.

How to Drop a Primary Key in SQL?

You can quickly drop a primary key constraint in all the databases using ALTER TABLE command. However, the syntax for MySQL and SQL Server/Oracle/MS Access is different.

Dropping Primary Key in SQL Server, Oracle, and MS Access

The syntax to drop a primary key is:

ALTER TABLE table_name

DROP CONSTRAINT PRIMARY_KEY_NAME

Let’s drop the primary key PK_Students we created for the Students table in the last section using the Oracle server.

ALTER TABLE Students

DROP CONSTRAINT PK_Students;

Output:

Now, insert the duplicate value that threw the error previously.

INSERT INTO Students VALUES (2, 'Chris', 'da.com');

SELECT * FROM Students;

Output:

As you can see in the output, it inserted the row in the table despite having the same ID and Email_ID column values. Thus, you can conclude that the primary key SK_Students was dropped.

Dropping Primary Key in MySQL

The syntax for dropping the primary key in MySQL is:

ALTER TABLE table_name

DROP CONSTRAINT PRIMARY KEY

Use the same Students table you have been using until now and drop the primary key from the ID column.

CREATE TABLE Students(

ID INT,

Name VARCHAR(20),

Email_ID NVARCHAR(25),

PRIMARY KEY(ID)

);

INSERT INTO Students VALUES (1, 'George', 'ge.com');

INSERT INTO Students VALUES (2, 'David', 'da.com');

INSERT INTO Students VALUES (3, 'Aakash', 'aa.com');

SELECT * FROM Students;

Output:

Let’s drop the primary key now and insert a duplicate to see if it is dropped appropriately or not.

ALTER TABLE Students

DROP PRIMARY KEY;

INSERT INTO VALUES (2, 'Chris', 'da.com');

SELECT * FROM Students;

Output:

Since the table accepted duplicate values for the ID column, you can confirm that it dropped the primary key.

Conclusion:

In this article, you learned everything about the SQL primary key. You have gone through how to create it in different databases at different levels using both CREATE TABLE and ALTER TABLE command. This article also explored how to drop a primary key in multiple databases. The primary key is an essential constraint that will help you maintain the integrity and authenticity of databases.

If you are keen on SQL and want to pursue a career in the field, you can opt for Simplilearn’s SQL Certification Course. The training provided all the learning materials and applied learning strategies on different types of SQL servers. Thus, it is adept at helping you excel in SQL database management.

Have any questions for us? Leave them in the comments section of this article. Our experts will get back to you on the same, ASAP!