Understanding Keys in SQL – 5 Different Types

In the realm of relational databases, keys play a pivotal role in ensuring data integrity, relationships, and efficient querying. Let’s explore the different types of keys by considering two hypothetical tables: ‘customer’ and ‘patient.’

Table Structures

Consider the following structures for the ‘customer’ and ‘patient’ tables:

Table 1: customer

Table 2: patient

1. Primary Key

A primary key is a column or set of columns that uniquely identifies each row in a database table. In the ‘customer’ table, both InsuranceID and National Insurance Number (NIN) could serve as primary keys. However, it is preferable to choose InsuranceID, considering that NIN might contain sensitive personal information.

-- Example of defining a primary key
CREATE TABLE customer (
  InsuranceID INT PRIMARY KEY,
  Name VARCHAR(50),
  DOB DATE,
  NIN VARCHAR(20) UNIQUE,
  Location VARCHAR(100)
);

2. Super Key

A super key is a set of columns that, taken together, uniquely identifies each row in a table. In the ‘customer’ table, a super key could be formed by combining InsuranceID and Name.

-- Example of defining a super key
CREATE TABLE customer (
  InsuranceID INT,
  Name VARCHAR(50),
  DOB DATE,
  NIN VARCHAR(20) UNIQUE,
  Location VARCHAR(100),
  PRIMARY KEY (InsuranceID),
  UNIQUE (InsuranceID, Name) -- Super Key
);

3. Foreign Key

A foreign key is a column in one table that references the primary key in another table. In the ‘patient’ table, InsuranceID serves as a foreign key, referencing the primary key in the ‘customer’ table.

-- Example of defining a foreign key
CREATE TABLE patient (
  HospitalID INT,
  Name VARCHAR(50),
  DOB DATE,
  InsuranceID INT,
  FOREIGN KEY (InsuranceID) REFERENCES customer(InsuranceID)
);

4. Unique Key

A unique key is a column or set of columns that uniquely identifies each row, similar to a primary key. However, unique keys have some distinctions:

– A unique key can accept only one null value, while a primary key cannot be null.
– Multiple unique keys can exist in a table, but there can only be one primary key.
– A primary key creates a clustered index, while a unique key creates a non-clustered index.

-- Example of defining a unique key
CREATE TABLE customer (
  InsuranceID INT PRIMARY KEY,
  Name VARCHAR(50),
  DOB DATE,
  NIN VARCHAR(20) UNIQUE,
  Location VARCHAR(100),
  UNIQUE (Name) -- Unique Key
);

5. Candidate Key

A candidate key is a column or set of columns eligible to become a primary key. In the ‘customer’ table, both InsuranceID and NIN can be considered candidate keys, but since InsuranceID is chosen as the primary key, NIN becomes a candidate key.

-- Example of defining a candidate key
CREATE TABLE customer (
  InsuranceID INT PRIMARY KEY,
  Name VARCHAR(50),
  DOB DATE,
  NIN VARCHAR(20) UNIQUE, -- Candidate Key
  Location VARCHAR(100)
);

Understanding these key concepts is fundamental for designing robust and efficient database structures, ensuring data accuracy and maintaining relationships between tables in a relational database system.

• How To Fix the Crowdstrike/BSOD Issue in Microsoft Windows
• MICROSOFT is Down Worldwide – Read Full Story
• Windows Showing Blue Screen Of Death Error? Here’s How You Can Fix It
• A Guide to SQL Operations: Selecting, Inserting, Updating, Deleting, Grouping, Ordering, Joining, and Using UNION
• Top 10 Most Common Software Vulnerabilities
• Essential Log Types for Effective SIEM Deployment
• How to Fix the VMware Workstation Error: “Unable to open kernel device ‘.\VMCIDev\VMX'”
• Top 3 Process Monitoring Tools for Malware Analysis
• CVE-2024-6387 – Critical OpenSSH Unauthenticated RCE Flaw ‘regreSSHion’ Exposes Millions of Linux Systems
• 22 Most Widely Used Testing Tools