Lecture Notes: Primary Key Constraint in MySQL

Overview

• Primary Key Constraint: A rule applied to a column in a MySQL table where each value must be both unique and not null.
• Purpose: Typically used as a unique identifier for rows within a table.

Key Concepts

• Uniqueness:
  • Each value in the primary key column must be unique.
  • Example: Social Security Number in the United States.
• Non-null:
  • No value in the primary key column can be null.
• Single Primary Key:
  • A table can only have one primary key constraint.
  • Example: You cannot have multiple columns as primary keys in the same table.

Example: Transaction Table

• Table Creation:
  • Create a Transactions table with the following columns:
    • transaction_id: Integer, set as the primary key.
    • amount: Decimal, with a max size of five digits and a precision of two.
• Constraints:
  • No duplicate transaction_id values.
  • No null values in the transaction_id column.

Syntax

• Adding Primary Key to a New Table:
  • Define it during table creation.
• Adding Primary Key to an Existing Table:
  • Syntax: ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> PRIMARY KEY (<column_name>);
  • Note: If a primary key exists, you cannot add another.

Practical Examples

• Inserting Rows:
  • Insert transactions with unique transaction_id values.
  • Example transactions:
    • transaction_id: 1000, amount: $4.99
    • transaction_id: 1001, amount: $2.89
    • transaction_id: 1002, amount: $3.38
  • Attempted duplicate transaction_id results in an error: Duplicate entry for key transactions.primary.
• Null Value Attempt:
  • Attempting to insert a row with transaction_id set to null results in an error: Column transaction ID cannot be null.

Use Case

• Unique Identification:
  • Useful for uniquely identifying transactions.
  • Allows easy lookup for operations like refunds using SELECT amount FROM transactions WHERE transaction_id = <value>.

Summary

• Primary Key:
  • Used as a unique identifier in every table.
  • Ensures each row is uniquely identifiable.
  • Essential for database integrity and reliable data management.