RDBMS Keys and Relationships

Keys and Relationships in RDBMS

When building a database, storing data in tables is only the first step. The real power of a Relational Database Management System comes from connecting those tables together. This is done using keys and relationships.

Keys help identify records, while relationships allow different tables to share information. Together, they keep your database organized, reduce duplicate data, and make it easier to retrieve information.

For example, customer information can be stored in one table, while order details are stored in another. A relationship between these tables tells the database which customer placed which order.

What are Keys?

A key is a column, or a group of columns, used to identify records or connect tables.

Think of a key like a house key. Just as one key opens a specific door, a database key helps locate a specific record or connect related records in different tables.

Keys improve data accuracy and prevent confusion when many records exist.

Primary Key

A primary key is a column that uniquely identifies each row in a table. Every record must have a different primary key value, and it cannot be empty.

For example, consider a Students table.

Here, StudentID is the primary key because every student has a unique ID. If two students had the same StudentID, the database would not know which record is correct.

Characteristics of a Primary Key

• Every value must be unique.
• It cannot contain NULL values.
• A table can have only one primary key.
• It helps identify records quickly.

Foreign Key

A foreign key is a column that creates a link between two tables. It stores the primary key value from another table.

Suppose we have two tables.

Students Table

Courses Table

In the Courses table, StudentID is a foreign key because it refers to the StudentID column in the Students table. This relationship tells us which student is enrolled in which course.

Why are Keys Important?

Keys play an important role in database management. They help to:

• Identify records uniquely.
• Connect related tables.
• Prevent duplicate records.
• Improve data accuracy.
• Speed up searching and retrieving information.
• Maintain consistency across the database.

What are Relationships?

A relationship is a connection between two or more tables using keys. Instead of storing the same information repeatedly, relationships allow tables to share data.

For example, Customers, Orders, and Products tables can work together through keys, making the database efficient and well organized.

Types of Relationships

One-to-One Relationship

In a one-to-one relationship, one record in the first table matches only one record in the second table.

Example: each employee has one company ID card.

One employee can have only one ID card, and one ID card belongs to only one employee.

One-to-Many Relationship

This is the most common type of relationship. One record in the first table can be connected to many records in the second table.

Example: one customer can place many orders.

Many-to-Many Relationship

In a many-to-many relationship, many records from one table are connected to many records in another table.

For example, students can enroll in many courses, and each course can have many students. To manage this relationship, an additional table is used.

The Enrollments table connects students and courses without storing duplicate information.

SQL Example

The following SQL code creates two related tables.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    StudentID INT,
    CourseName VARCHAR(100),
    FOREIGN KEY (StudentID)
        REFERENCES Students(StudentID)
);

In this example, StudentID is the primary key in the Students table. The StudentID column in the Courses table is a foreign key. The foreign key creates a relationship between the two tables.

This ensures that every student referenced in the Courses table already exists in the Students table.