What is 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 tables that apply them.

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 follow the set constraints, the operation is canceled, and an error is returned.

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 above 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 above 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 above syntax.

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

SQL Drop Primary Key

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

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

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

Conclusion

In this article, we 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.

Thanks for reading!!