WikiGalaxy
Primary Key
Definition
A primary key is a unique identifier for each record in a database table. It ensures that no two rows have the same key value and that the key is not null.
Characteristics
Primary keys must contain unique values and cannot contain NULLs. They are often set on a single column, but can also be composed of multiple columns (composite key).
CREATE TABLE Students (
StudentID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
PRIMARY KEY (StudentID)
);
Advantages
Ensures data integrity by uniquely identifying records and preventing duplicate entries.
Limitations
Cannot be null and must be unique, which might not be suitable for all data types or situations.
Console Output:
Table created with StudentID as the primary key.
Foreign Key
Definition
A foreign key is a field in a table that uniquely identifies a row of another table. The foreign key is defined in a second table, but it references the primary key or a unique key in the first table.
Purpose
To maintain referential integrity between two tables by ensuring that the value in one table corresponds to a valid entry in another table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
CustomerID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Benefits
Ensures that relationships between tables remain consistent, preventing orphaned records and maintaining data integrity.
Challenges
Can lead to complex relationships and require careful planning to avoid circular references and cascading deletes.
Console Output:
Table created with CustomerID as a foreign key referencing Customers table.
Unique Key
Definition
A unique key is a constraint that ensures all values in a column are different. It allows NULL values, but only one per column.
Usage
Used to enforce the uniqueness of the column values, similar to a primary key but allows for a single NULL entry.
CREATE TABLE Employees (
EmployeeID int NOT NULL,
Email varchar(255),
UNIQUE (Email)
);
Advantages
Prevents duplicate entries in a column, ensuring data accuracy and integrity.
Considerations
Allows NULL, which can be a limitation if uniqueness is required for all entries.
Console Output:
Table created with Email as a unique key.
Composite Key
Definition
A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table.
Application
Useful when a single column is not sufficient to uniquely identify records in a table.
CREATE TABLE CourseRegistrations (
StudentID int NOT NULL,
CourseID int NOT NULL,
RegistrationDate date,
PRIMARY KEY (StudentID, CourseID)
);
Benefits
Allows for more complex keys that can handle multiple attributes, increasing flexibility in database design.
Drawbacks
Can complicate queries and indexing due to the involvement of multiple columns.
Console Output:
Table created with a composite key of StudentID and CourseID.
Candidate Key
Definition
A candidate key is a column, or a set of columns, that can uniquely identify a row in a table. It is a potential choice for the primary key.
Role
All candidate keys can be considered as potential primary keys. A table can have multiple candidate keys but only one primary key.
CREATE TABLE Books (
ISBN varchar(13) NOT NULL,
Title varchar(255) NOT NULL,
Author varchar(255),
PRIMARY KEY (ISBN),
UNIQUE (Title)
);
Importance
Provides flexibility in choosing the best primary key based on database requirements and performance considerations.
Challenges
Selecting the most efficient candidate key can be complex, especially in large databases with multiple candidate keys.
Console Output:
Table created with ISBN as the primary key and Title as a candidate key.
Super Key
Definition
A super key is a set of one or more columns that can uniquely identify a row in a table. It includes all candidate keys and primary keys.
Usage
Used to identify all possible keys that can uniquely identify rows in a table, providing a broader scope than candidate keys.
CREATE TABLE Vehicles (
VehicleID int NOT NULL,
LicensePlate varchar(10) NOT NULL,
VIN varchar(17),
PRIMARY KEY (VehicleID),
UNIQUE (LicensePlate, VIN)
);
Advantages
Helps in identifying all key combinations that can uniquely identify records, aiding in database normalization.
Limitations
Can result in redundancy and inefficiency if not properly managed, as super keys include unnecessary attributes.
Console Output:
Table created with VehicleID as the primary key and LicensePlate, VIN as a super key.
Alternate Key
Definition
An alternate key is any candidate key that is not selected as the primary key. It is an alternative option to uniquely identify records.
Function
Acts as a backup method for accessing records uniquely, providing additional flexibility in database management.
CREATE TABLE Users (
UserID int NOT NULL,
Username varchar(255) NOT NULL,
Email varchar(255),
PRIMARY KEY (UserID),
UNIQUE (Username, Email)
);
Benefits
Provides multiple access paths to data, enhancing query flexibility and performance.
Considerations
Managing multiple keys can increase complexity and require careful planning to ensure efficiency.
Console Output:
Table created with UserID as the primary key and Username, Email as alternate keys.
Surrogate Key
Definition
A surrogate key is a unique identifier for each row in a table, typically generated by the database system. It has no business meaning.
Implementation
Often implemented as an auto-incrementing integer, providing a simple and efficient way to uniquely identify records.
CREATE TABLE Orders (
OrderID int NOT NULL AUTO_INCREMENT,
OrderDate date,
CustomerID int,
PRIMARY KEY (OrderID)
);
Advantages
Simplifies key management by providing a consistent and unique identifier for each record without relying on business data.
Drawbacks
Lacks business meaning, which can complicate data interpretation and require additional joins to retrieve meaningful information.
Console Output:
Table created with OrderID as a surrogate key.
