SQL – Primary Key and its use in a Database

In this article you will learn about-

• What is a primary key?
• How to create primary key constraint?
• How to drop primary key constraint?

What is primary Key?

• The Primary key is a column or a group of columns to uniquely identify a record in a table.
• Primary keys must contain unique values, and cannot contain null values.
• A table can have only one primary key.
• The primary key can consist of single or multiple columns called composite key.

 A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Databases use keys to compare, sort, and store records, and to create relationships between records. Choosing the primary key in a database is one of the most important steps in the process. It can be a normal attribute that is guaranteed to be unique such as AADHAAR No on a table with no more than one record per person or — preferably — it can be generated by the database management system such as ID in MySQL.

Why are primary keys important?

Primary keys are essential in relational databases because these keys are used to search, compare, sort and otherwise define relationships between individual records.

Using a CREATE TABLE statement

Syntax:

The syntax for creating a primary key using a CREATE TABLE statement is:

 CREATE TABLE tableName (
   column1 data_type,
   column2 data_type,
    ...
  CONSTRAINT "constraint_name" PRIMARY KEY (column,column2,…)
);

Example:

 CREATE TABLE "BMS"."PRODUCT"
   (        "DOE" VARCHAR2(50 BYTE) NOT NULL ENABLE,
            "CURRENTYEAR" VARCHAR2(10 BYTE),
            "HEATID" VARCHAR2(8 BYTE),
            "CASTER_NO" NUMBER(2,0) NOT NULL ENABLE,
            "FORMAT" VARCHAR2(30 BYTE) NOT NULL ENABLE,
            "LENGTH" NUMBER(3,0) NOT NULL ENABLE,
            "STEELGRADE" VARCHAR2(200 BYTE) NOT NULL ENABLE,
            "PRODUCTION" NUMBER(4,0) NOT NULL ENABLE,
            "MN_BAY_COUNT" NUMBER(4,0),
            "MN_BAY_WEIGHT" NUMBER(5,0),
            "NP_BAY_COUNT" NUMBER(4,0),
            "NP_BAY_WEIGHT" NUMBER(5,0),
            "PQ_BAY_COUNT" NUMBER(4,0),
            "PQ_BAY_WEIGHT" NUMBER(5,0),
            "QR_BAY_COUNT" NUMBER(4,0),
            "QR_BAY_WEIGHT" NUMBER(5,0),
            "TONNAGE" NUMBER(10,3),
             CONSTRAINT "PRODUCT_PK" PRIMARY KEY ("HEATID", "CURRENTYEAR")
);

Using an ALTER TABLE statement

Syntax:

 ALTER TABLE tableName
  ADD CONSTRAINT constraint_name
   PRIMARY KEY (column1,column2,....);

Example:

ALTER TABLE PRODUCT
  ADD CONSTRAINT PRODUCT _PK
   PRIMARY KEY (HEATID, CURRENTYEAR);

Droping a primary key constraint

To drop a primary key, use following statement-   

Syntax:

ALTER TABLE tableName
   DROP CONSTRAINT constraint_name;

Example:

ALTER TABLE PRODUCT
   DROP CONSTRAINT PRODUCT _PK;

Points to note:

(1). Columns in primary key must not be null.

Kindly comment and share if you liked the article.