In SQL, keys are important concepts that help us uniquely identify and organize data in a database. There are several types of keys in SQL, each serving a different purpose. Let’s explore these keys with simple explanations and SQL code examples.
Types of Keys in SQL
| Key Type | Description | SQL Code Example |
|---|---|---|
| Primary Key | Uniquely identifies each record in a table. | CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50)); |
| Foreign Key | Links two tables together, enforcing referential integrity. | CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)); |
| Unique Key | Ensures that all values in a column are unique. | CREATE TABLE Employees (EmployeeID INT UNIQUE, Name VARCHAR(50)); |
| Composite Key | Uses multiple columns to create a unique identifier. | CREATE TABLE Invoices (InvoiceID INT, ProductID INT, PRIMARY KEY (InvoiceID, ProductID)); |
| Candidate Key | A key that could become the primary key. | CREATE TABLE Books (ISBN VARCHAR(13) PRIMARY KEY, Title VARCHAR(100), CandidateKey VARCHAR(10)); |
Brief Summary
- Primary Key: Uniquely identifies records in a table.
- Foreign Key: Links tables together, maintaining data integrity.
- Unique Key: Ensures column values are unique.
- Composite Key: Uses multiple columns for uniqueness.
- Candidate Key: A potential primary key candidate.
Keys are essential in SQL databases for organizing data and maintaining data integrity. They ensure that data remains accurate and meaningful throughout the database.
key in SQL with detailed explanations and examples.
1. Primary Key
Description: A Primary Key uniquely identifies each record (row) in a table. It ensures that there are no duplicate values in the key column(s).
SQL Code Example:
CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(50) );
In this example, the StudentID column is designated as the primary key, guaranteeing that each student has a unique identifier.
2. Foreign Key
Description: A Foreign Key links two tables together by establishing a relationship between them. It enforces referential integrity, ensuring that values in the foreign key column(s) match values in the primary key of another table.
SQL Code Example:
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );
Here, the CustomerID column in the Orders table is a foreign key that references the CustomerID column in the Customers table. This ensures that an order is associated with a valid customer.
3. Unique Key
Description: A Unique Key ensures that all values in a specified column(s) are unique, but unlike the primary key, it allows for NULL values.
SQL Code Example:
CREATE TABLE Employees ( EmployeeID INT UNIQUE, Name VARCHAR(50) );
In this example, the EmployeeID column must contain unique values. However, it can have NULL values because it’s not the primary key.
4. Composite Key
Description: A Composite Key uses multiple columns together to create a unique identifier for each record. It ensures uniqueness when combined but not necessarily individually.
SQL Code Example:
CREATE TABLE Invoices ( InvoiceID INT, ProductID INT, PRIMARY KEY (InvoiceID, ProductID) );
Here, the combination of InvoiceID and ProductID forms a composite primary key. This means that each combination of these two columns must be unique.
5. Candidate Key
Description: A Candidate Key is a column or set of columns that could potentially become the primary key. It has the unique constraint but is not necessarily chosen as the primary key.
SQL Code Example:
CREATE TABLE Books ( ISBN VARCHAR(13) PRIMARY KEY, Title VARCHAR(100), CandidateKey VARCHAR(10) UNIQUE );
In this case, ISBN is the chosen primary key, but CandidateKey is a candidate for the primary key because it has the unique constraint.
Each type of key in SQL serves a specific purpose in database design, allowing for data organization and integrity based on your requirements.
