Lädt...


🔧 Keys in SQL | Primary Key | Foreign Key| Candidate Key|Composite Key |Alternate Key|Super Key|Surrogate Key| Unique Key


Nachrichtenbereich: 🔧 Programmierung
🔗 Quelle: dev.to

In SQL, keys are used to identify rows in a table uniquely, enforce relationships between tables, and maintain data integrity. There are different types of keys, each serving a specific purpose. Below is a breakdown of various keys with simple examples:

  1. Primary Key

A primary key uniquely identifies each record in a table. It cannot have NULL values, and there can only be one primary key per table.

Example:

CREATE TABLE Students (
StudentID INT PRIMARY KEY, -- Primary Key
Name VARCHAR(50),
Age INT
);

Here, StudentID is the primary key, meaning each StudentID value must be unique and non-null.

  1. Foreign Key

A foreign key is a column (or set of columns) that creates a relationship between two tables. It points to the primary key of another table, enforcing referential integrity.

Example:

CREATE TABLE Courses (
CourseID INT PRIMARY KEY, -- Primary Key
CourseName VARCHAR(50)
);

CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) -- Foreign Key
);

Here, CourseID in the Enrollments table is a foreign key that references the CourseID in the Courses table.

  1. Candidate Key

A candidate key is a column (or set of columns) that can uniquely identify a row in a table. A table can have multiple candidate keys, but only one of them is chosen as the primary key.

Example:

CREATE TABLE Employees (
EmployeeID INT, -- Candidate Key
Email VARCHAR(100), -- Candidate Key
Name VARCHAR(50),
PRIMARY KEY (EmployeeID) -- Chosen as the Primary Key
);

In this table, both EmployeeID and Email can uniquely identify a row, so they are candidate keys. However, EmployeeID is chosen as the primary key.

  1. Composite Key

A composite key is a primary key composed of two or more columns, which together uniquely identify a row in the table.

Example:

CREATE TABLE Orders (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID) -- Composite Key
);

Here, both OrderID and ProductID together form a composite key. No two rows can have the same combination of OrderID and ProductID.

  1. Alternate Key

An alternate key is any candidate key that is not selected as the primary key.

Example:

CREATE TABLE Vehicles (
VehicleID INT, -- Primary Key
LicensePlate VARCHAR(50), -- Alternate Key
OwnerName VARCHAR(50),
PRIMARY KEY (VehicleID)
);

Here, LicensePlate is a candidate key but not the primary key, so it is an alternate key.

  1. Unique Key

A unique key ensures that all values in a column or set of columns are distinct from each other. Unlike the primary key, a unique key can have NULL values.

Example:

CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE, -- Unique Key
Username VARCHAR(50)
);

Here, Email is a unique key, ensuring that no two users have the same email address, but it can allow NULL values (if desired).

  1. Super Key

A super key is a set of one or more columns that can uniquely identify a row in a table. A candidate key is a minimal super key (i.e., no column can be removed from it without losing the uniqueness property).

Example:

In the Employees table:

(EmployeeID, Email) -- Super Key (as EmployeeID and Email together can uniquely identify rows)

Any combination that can uniquely identify rows, such as EmployeeID or Email alone, or both together, can be considered a super key.

  1. Surrogate Key

A surrogate key is an artificial key used as a unique identifier for a table. It's often a system-generated value like an auto-incrementing integer that has no real-world meaning.

Example:

CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT, -- Surrogate Key
ProductName VARCHAR(50),
Quantity INT,
PRIMARY KEY (OrderID)
);

Here, OrderID is a surrogate key, typically generated by the database system, without any direct business meaning.

Summary of Keys:

Each of these keys plays a crucial role in designing well-structured databases that ensure data integrity and efficient querying.

...

📰 Data Model Design 101: Composite vs Surrogate Keys


📈 54.09 Punkte
🔧 AI Nachrichten

🪟 How to Fix COM Surrogate Process Issues: What Is COM Surrogate?


📈 50.91 Punkte
🪟 Windows Tipps

📰 How to Fix COM Surrogate Process Issues: What Is COM Surrogate?


📈 50.91 Punkte
🖥️ Betriebssysteme

🔧 Understanding Primary Keys and Foreign Keys in SQL: A Simple and Detailed Guide


📈 49.11 Punkte
🔧 Programmierung

🔧 Surrogate Key vs Primary Key: What's the Difference?


📈 46.03 Punkte
🔧 Programmierung

🔧 Why are foreign keys and primary keys used in databases?


📈 45.85 Punkte
🔧 Programmierung

🔧 Types of constraints (primary key, foreign key, unique, check, and not null)


📈 44.87 Punkte
🔧 Programmierung

🔧 Upsert a row in a DB that doesn't use primary keys or unique constraints


📈 37.01 Punkte
🔧 Programmierung

🔧 Primary Key and Foreign Key in SQL | Part 2


📈 35.95 Punkte
🔧 Programmierung

🔧 Day 38 - Primary key, Foreign key in Database, Types of SQL Languages


📈 35.95 Punkte
🔧 Programmierung

🔧 Primary Key and Foreign Key in SQL | Part 1


📈 35.95 Punkte
🔧 Programmierung

📰 Collision Risk in Hash-Based Surrogate Keys


📈 34.35 Punkte
🔧 AI Nachrichten

🕵️ Medium CVE-2019-12893: Alternate-tools Alternate pic view


📈 30 Punkte
🕵️ Sicherheitslücken

🕵️ Medium CVE-2019-12894: Alternate-tools Alternate pic view


📈 30 Punkte
🕵️ Sicherheitslücken

🕵️ Medium CVE-2019-12895: Alternate-tools Alternate pic view


📈 30 Punkte
🕵️ Sicherheitslücken

🐧 DynamoDB Composite Keys: Everything You Should Know


📈 28.64 Punkte
🐧 Linux Tipps

📰 California Dem Candidate DDoS’d During Failed Primary Bid


📈 28.4 Punkte
📰 IT Security Nachrichten

📰 From Surrogate Modelling to Aerospace Engineering: a NASA Case Study


📈 25.45 Punkte
🔧 AI Nachrichten

📰 Optimization with Surrogate Models via Symbolic Regression


📈 25.45 Punkte
🔧 AI Nachrichten

🪟 Fix: COM Surrogate High CPU and Memory Usage [6 Ways]


📈 25.45 Punkte
🪟 Windows Tipps

📰 What is COM Surrogate? How to Fix Issues With It?


📈 25.45 Punkte
📰 IT Security Nachrichten

📰 What is COM Surrogate? How to Fix Issues With It?


📈 25.45 Punkte
📰 IT Security Nachrichten

📰 Genetically-Modified 'Surrogate Hens' Could Lay Eggs of Rare Chicken Breeds, Scientists Say


📈 25.45 Punkte
📰 IT Security Nachrichten

📰 From Surrogate Modelling to Aerospace Engineering: a NASA Case Study


📈 25.45 Punkte
🔧 AI Nachrichten

📰 Exploring the Right Fit: Choosing Primary Keys for Your Database


📈 24.84 Punkte
🔧 AI Nachrichten

🔧 Instant API for databases adds automatic primary keys


📈 24.84 Punkte
🔧 Programmierung

🔧 How Missing Primary Keys Break Your Galera Cluster


📈 24.84 Punkte
🔧 Programmierung

🔧 How Missing Primary Keys Break Your Galera Cluster


📈 24.84 Punkte
🔧 Programmierung

📰 Wordlist_Generator - Unique Wordlist Generator Of Unique Wordlists


📈 24.34 Punkte
📰 IT Security Nachrichten

matomo