Difference between Primary key Unique Key in DB2

In IBM Db2, the concepts of primary key and unique key constraints are similar to other relational database management systems (RDBMS) like Oracle, Microsoft SQL Server, and MySQL. Let's explore the differences with examples:

1. Primary Key:

   - A primary key uniquely identifies each record in a table and ensures that the column(s) it is applied to does not contain duplicate or NULL values.

   - Each table can have only one primary key.

   - Db2 automatically creates a unique index on the primary key column(s).

   - It's typically used for the main identifier of a table.

Example:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

);

In this example, EmployeeID is the primary key, ensuring each employee has a unique identifier.

2. Unique Key:

   - A unique key constraint ensures that all values in a column or a set of columns are unique within the table. Unlike primary keys, unique keys can contain NULL values.

   - Multiple unique key constraints can exist within a single table.

   - Db2 also automatically creates a unique index on the unique key column(s).

   - It's used when you want to ensure uniqueness but don't need the primary key constraints.

Example:

CREATE TABLE Students (

    StudentID INT,

    StudentEmail VARCHAR(100) UNIQUE,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

);

In this example, StudentEmail is a unique key, ensuring that each email address stored in the table is unique, but it allows for NULL values.

In summary, the differences between primary key and unique key constraints in Db2 are similar to other RDBMS systems. Primary keys are stricter in disallowing NULL values and are typically used as the main identifier, while unique keys can allow NULLs and are used for other unique constraints.