Relationships Between Tables

Once you’ve created tables, the next step is connecting them to create a meaningful database structure. Relationships between tables allow you to link data, enabling complex queries and efficient data management. In this article, we’ll discuss relationships between tables with examples and syntax for SQL Server, MySQL, and Oracle PL/SQL.

​

What Are Table Relationships?

A relationship is a connection between two tables based on a common column. For example, a Customers table might relate to an Orders table through a column like customer_id.

​

Types of Relationships

1. One-to-One: Each row in one table corresponds to exactly one row in another.
2. One-to-Many: One row in one table corresponds to multiple rows in another.
3. Many-to-Many: Rows in one table can correspond to multiple rows in another, often handled using a join table.

​

Defining Relationships Using Foreign Keys

A foreign key is a column in one table that references a primary key in another table. This establishes the link between the tables.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

​

Querying Data Across Tables

Once relationships are defined, you can write queries to fetch data from multiple tables using joins.

Fetch the names of customers along with their order dates:

SELECT c.customer_name , o.order_date 
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;

​

One-to-Many Relationship Example

In our example, one customer can have many orders. The customer_idcolumn in the Orders table references the primary key in the Customers table.

​

Insert Sample Data

INSERT INTO customers (customer_id, customer_name)
VALUES (1, 'Raja'), (2, 'Teja');

INSERT INTO orders (order_id, order_date, customer_id)
VALUES 
(101, '2025-01-01', 1),
(102, '2025-01-02', 1);

Query Result

​

Understanding Referential Integrity

Foreign keys help maintain referential integrity, ensuring that:

1. You can’t insert a customer_id in orders that doesn’t exist in customers.
2. You can’t delete a row in customers if related rows exist in orders unless cascading actions are defined.

​

Summary

Defining relationships between tables is a cornerstone of database design. By using foreign keys, you create a structure that enforces data integrity and enables powerful queries. In the next article, we’ll dive into constraints, exploring how to enforce rules and validate data within your tables.