×

Courses

Chapters

>
<

DBMS

DBMS Key Primary | CrackEase

Primary Key in DBMS

Primary Key

Sometimes you need to ensure each row (record) in a database table is unique. A primary key enforces that uniqueness on one or more column(s).

Formally, a primary key is a constraint that uniquely identifies each row in a table by designating one or more columns as the primary key.

Primary Key
Primary key properties

  • A primary key column must contain unique values.
  • Null values are not allowed for the primary key column.
  • Only one primary key is allowed per table (but it can be composite — formed by more than one column).

Examples of suitable primary keys

  • Student ID in a student table — no two students will have the same ID.
  • Employee ID in an employee table — unique per employee.
  • Not suitable: Age, name or phone number are generally poor choices because duplicates are possible.

Properties
Creating a primary key

A column is designated as the primary key using PRIMARY KEY(column_name) in the CREATE TABLE statement.

  
CREATE TABLE EMP
(
   ID        INT,
   NAME      VARCHAR(20),
   AGE       INT,
   ADDRESS   CHAR(25),
   SALARY    DECIMAL(18,2),
   PRIMARY KEY (ID)
);

  • With PRIMARY KEY(ID), the ID column must be unique for every row.
  • Inserting a duplicate ID will cause an error.
  • A primary key enforces both uniqueness and not-null on the designated column(s).

Students Table (example)

Stu_IdStu_NameStu_Age
101Steve23
102John24
103Robert28
104Steve29
105Carl29
Primary key with more than one attribute (composite primary key)

  • Consider an order table with attributes: Customer_ID, Product_ID, Order_Quantity.
  • Customer_ID alone cannot be the primary key because a customer can place multiple orders (Customer_ID repeats).
  • Similarly, Product_ID alone may repeat across orders.
  • Combining attributes such as (Customer_ID, Product_ID) can uniquely identify each order line — this is a composite primary key.

Customer_IDProduct_IDOrder_Quantity
66902310
67902315
68903120
69903118
66911150

  • None of the single attributes alone uniquely identify each row in the example above.
  • Using the combination (Customer_ID, Product_ID) will guarantee unique identification for each order line.

SQL Example — composite primary key

  
CREATE TABLE "ORDER"
(
    Customer_ID    INT,
    Product_ID     INT,
    Order_Quantity INT,
    PRIMARY KEY (Customer_ID, Product_ID)
);

  • When choosing a primary key, prefer the minimal set of attributes that ensures uniqueness.
  • If two candidate sets uniquely identify rows, choose the one with fewer attributes (the minimal set).

Footer Content | CrackEase
→
←