SQL Standard

What Is the Primary Key in SQL?

In SQL, a constraint refers to rules and policies implemented by a database table in a relational database. These rules and policies help govern how data is manipulated in the applicable tables.

The principal purpose of SQL constraints is to enhance accuracy and data consistency providing reliability of the data stored in the set tables. Hence, constraints can help to provide data integrity in the tables.

If a table implements a specific constraint, all queries applied to that table must abide by the set rules. If a query does not follow the set of constraints, the operation is canceled, and an error is returned.

There are various types of SQL constraints. However, this tutorial will discuss one of the most valuable and popular SQL constraints: the Primary Key.

SQL Primary Key

SQL Primary key is a type of constraint applied to a specific field in a database table. The primary key constraints uniquely identified each record in the table. This ensures there are no duplicate values or null records in the table.

SQL supports only one primary key per table. However, remember that a primary key can consist of single or multiple fields known as a composite key.

Applying the primary key constraint when creating a table is highly recommended.

SQL Create Primary Key

We can create a primary key constraint on a table during creation, as shown in the sample command below:

CREATE TABLE TABLE_NAME (
    field_1 data_type NOT NULL,
    field_2 data_type NOT NULL,
    field_3 data_type,
    CONSTRAINT PK_name PRIMARY KEY(field_1, field_2)
)

We create a primary key constraint with multiple fields in the previous syntax. We can create a primary key with a single field, as shown in the syntax below:

CREATE TABLE TABLE_NAME (
    field_1 data_type NOT NULL,
    field_2 data_type NOT NULL,
    field_3 data_type,
    PRIMARY KEY(field_1)
)

Keep in mind that the previous syntax is standard SQL. Various database engines may provide different syntax for adding a primary key constraint.

SQL Add Primary Key

You can add a primary key constraint using the alter table command if a table already exists. This method is not recommended as a table may already contain duplicates or null values. This can cause issues when the primary key constraint is set.

To add a primary key constraint to an existing table, run the query as shown:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT pk_name PRIMARY KEY(FIELD(s))

We add a primary key to an existing table using the ALTER TABLE command in the previous syntax.

NOTE: If a specified field does not have a NOT NULL constraint, the previous statement will fail.

SQL Drop Primary Key

You can also delete a primary key constraint using the DROP statement. An example syntax is shown below:

-- drop constraint
ALTER TABLE TABLE_NAME DROP CONSTRAINT Pk_name;
ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

The previous example shows two methods of deleting a primary key constraint from a database table.

Conclusion

This article explored how to create an SQL primary key constraint, add a constraint to an existing table, and delete a primary key constraint in a table. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list