Understanding Different Types of Database Keys with SQL and PostgreSQL Syntax

sql server post gree sql

Understanding Different Types of Database Keys with SQL and PostgreSQL Syntax

1. Primary Key

A primary key uniquely identifies each record in a table. It ensures that no two rows share the same value for the primary key column, and it also enforces the NOT NULL constraint.

Key Characteristics:

  • Must be unique across the table.
  • Cannot contain NULL values.
  • A table can have only one primary key.

SQL Syntax (Creating Table with Primary Key):

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

PostgreSQL Syntax:

CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

Adding a Primary Key to an Existing Table:

ALTER TABLE Employees
ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeID);

2. Unique Key

A unique key ensures that all values in a column (or group of columns) are distinct. Unlike a primary key, a unique key allows one NULL value.

Key Characteristics:

  • Guarantees unique values across the table.
  • Allows a single NULL value.
  • A table can have multiple unique keys.

SQL Syntax (Creating Table with Unique Key):

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

PostgreSQL Syntax:

CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

Adding a Unique Key to an Existing Table:

ALTER TABLE Employees
ADD CONSTRAINT unique_email UNIQUE (Email);

3. Foreign Key

A foreign key creates a relationship between two tables by referencing the primary key of another table. This key ensures referential integrity by enforcing a link between the two tables.

Key Characteristics:

  • Ensures referential integrity between tables.
  • Allows multiple foreign keys in one table.
  • Can allow NULL values unless restricted.

SQL Syntax (Creating Table with Foreign Key):

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

PostgreSQL Syntax:

CREATE TABLE Departments (
    DepartmentID SERIAL PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Adding a Foreign Key to an Existing Table:

ALTER TABLE Employees
ADD CONSTRAINT fk_department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

4. Composite Key

A composite key is a combination of two or more columns that uniquely identify a row. It is used when no single column can uniquely identify a row.

Key Characteristics:

  • Combines multiple columns to create a unique identifier.
  • Useful when a natural key is composed of multiple fields.

SQL Syntax (Creating Table with Composite Key):

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

PostgreSQL Syntax:

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

Adding a Composite Key to an Existing Table:

ALTER TABLE Enrollments
ADD CONSTRAINT pk_enrollment PRIMARY KEY (StudentID, CourseID);

5. Candidate Key

A candidate key is any column or set of columns that can uniquely identify rows in a table. A table may have multiple candidate keys, one of which will be selected as the primary key.

6. Super Key

A super key is a set of one or more columns that can uniquely identify rows in a table. It includes all keys that uniquely identify rows, including primary keys and candidate keys.

7. Alternate Key

An alternate key is any candidate key that is not chosen as the primary key. While it still uniquely identifies rows, it serves as a backup identifier.

8. Surrogate Key

A surrogate key is an artificial, system-generated key (usually a numeric value) used as a unique identifier. It has no real-world meaning and is typically used when no natural primary key exists.

Key Characteristics:

  • Typically auto-generated using AUTO_INCREMENT (SQL) or SERIAL (PostgreSQL).
  • Does not carry business meaning.

SQL Syntax (Creating Table with Surrogate Key):

CREATE TABLE Employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

PostgreSQL Syntax:

CREATE TABLE Employees (
    ID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Summary

Understanding database keys is essential for designing a relational database that ensures data integrity and efficient relationships between tables. Here’s a quick recap of the key types:

  • Primary Key: Ensures each row is uniquely identified.
  • Unique Key: Guarantees column uniqueness, allowing one NULL.
  • Foreign Key: Establishes relationships between tables.
  • Composite Key: Combines multiple columns for unique identification.
  • Candidate Key: A potential primary key.
  • Super Key: Any set of columns that uniquely identifies rows.
  • Alternate Key: A candidate key that isn’t the primary key.
  • Surrogate Key: A system-generated identifier.

In both SQL and PostgreSQL, you can define these keys either at the time of table creation or by altering existing tables. This flexibility ensures that you can enforce proper data constraints as your database evolves.

Tags: , , , , , , , , , , ,