Key Constraints

Introduction to Key Constraints

Key constraints are rules that uniquely identify records within a database table and establish relationships between tables. They are fundamental to relational database design and ensure data integrity and efficient data retrieval.

Types of Keys in Relational Databases

1. Primary Key

A primary key is a column or combination of columns that uniquely identifies each row in a table.

Characteristics:

Must contain unique values (no duplicates)
Cannot contain NULL values
Each table can have only one primary key
Should be compact and efficient

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

2. Candidate Key

A candidate key is a column or set of columns that could potentially serve as the primary key for a table. All candidate keys have the properties of uniqueness and non-nullability.

Example: In a Students table, both StudentID and Email might be candidate keys because both can uniquely identify a student. Only one will be chosen as the primary key, while the other becomes an alternate key.

3. Alternate Key

An alternate key is a candidate key that is not selected as the primary key. Alternate keys are often implemented as unique constraints.

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,  -- Alternate key
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

4. Super Key

A super key is any set of columns that uniquely identifies rows in a table. It may contain additional columns that are not necessary for unique identification.

Example: In a Students table, {StudentID, FirstName, LastName} would be a super key if StudentID alone is already unique. The FirstName and LastName attributes are unnecessary for uniqueness but don’t invalidate the key.

5. Foreign Key

A foreign key is a column or group of columns in one table that references the primary key of another table, creating a relationship between the tables.

Example:

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

6. Composite Key

A composite key is a key that consists of two or more columns. It can be a primary key, candidate key, or foreign key.

Example:

CREATE TABLE CourseAssignments (
    CourseID INT,
    AssignmentNumber INT,
    Title VARCHAR(100),
    DueDate DATE,
    PRIMARY KEY (CourseID, AssignmentNumber)
);

Implementing Key Constraints in SQL

Primary Key Constraint

There are two ways to define a primary key in SQL:

Column-level constraint:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

Table-level constraint:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2),
    PRIMARY KEY (ProductID)
);

For composite primary keys, you must use a table-level constraint:

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Unique Constraint

Unique constraints ensure that values in a column or combination of columns are unique across the table. Unlike primary keys, unique constraints can contain NULL values (though only one NULL value is allowed in most database systems).

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(15) UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Multiple columns can be combined in a unique constraint:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    PositionID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    UNIQUE (DepartmentID, PositionID)
);

Foreign Key Constraint

Foreign keys enforce referential integrity between tables:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

You can also name constraints for easier management:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) 
        REFERENCES Customers(CustomerID)
);

Adding or Removing Key Constraints

Adding Constraints to Existing Tables

-- Adding a primary key
ALTER TABLE Products
ADD PRIMARY KEY (ProductID);

-- Adding a unique constraint
ALTER TABLE Customers
ADD CONSTRAINT UQ_Email UNIQUE (Email);

-- Adding a foreign key
ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrder 
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Removing Constraints

-- Removing a primary key
ALTER TABLE Products
DROP PRIMARY KEY;

-- Removing a unique constraint
ALTER TABLE Customers
DROP CONSTRAINT UQ_Email;

-- Removing a foreign key
ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerOrder;

Best Practices for Key Constraints

Choose appropriate primary keys
- Use surrogate keys (auto-incremented integers or GUIDs) for stability
- Ensure keys remain static over time
- Keep key size small for better performance
Use natural unique constraints
- Apply unique constraints on business-relevant unique attributes
- Use composite unique constraints when necessary
Design foreign keys carefully
- Consider appropriate referential actions (CASCADE, SET NULL, etc.)
- Index foreign key columns for better performance
- Document relationships between tables
Name constraints meaningfully
- Use consistent naming conventions
- Include table and column names in constraint names
Plan for performance
- Understand that constraints add overhead to DML operations
- Consider deferring constraint checking for bulk operations
- Monitor and optimize constraint-related performance issues

Common Issues and Troubleshooting

Violation of Key Constraints

Primary Key Violation
- Caused by: Duplicate values or NULL values in primary key columns
- Solution: Ensure values are unique and not NULL
Unique Constraint Violation
- Caused by: Duplicate values in unique columns
- Solution: Provide unique values or handle the duplication
Foreign Key Violation
- Caused by: Invalid references or operations on referenced data
- Solution: Ensure referential integrity or use appropriate referential actions

Performance Considerations

Indexes are automatically created for primary keys and unique constraints
Foreign key columns should be indexed manually for better performance
Composite keys can impact performance if they are large or used frequently
Constraint checking adds overhead to data modification operations

Benefits of Key Constraints

Data Integrity: Prevents invalid or duplicate data
Relationship Enforcement: Maintains valid references between tables
Improved Query Performance: Keys are typically indexed
Database Design Clarity: Documents the relationships and unique identifiers
Application Simplicity: Moves validation from application code to the database

Key constraints are essential for maintaining the integrity and structure of relational databases, providing a foundation for reliable data storage and retrieval.

Key Constraints

Introduction to Key Constraints

Types of Keys in Relational Databases

1. Primary Key

2. Candidate Key

3. Alternate Key

4. Super Key

5. Foreign Key

6. Composite Key

Implementing Key Constraints in SQL

Primary Key Constraint

Unique Constraint

Foreign Key Constraint

Adding or Removing Key Constraints

Adding Constraints to Existing Tables

Removing Constraints

Best Practices for Key Constraints

Common Issues and Troubleshooting

Violation of Key Constraints

Performance Considerations

Benefits of Key Constraints

Install Notes App