MULTIPLE TABLES THAT ARE CONNECTED BY FOREIGN KEYS
In SQL, you can establish relationships between multiple tables using foreign keys. A foreign key is a column or a set of columns in one table that refers to the primary key of another table. This relationship ensures data integrity and allows you to establish associations between related records. Here’s an example of how to create multiple tables connected by foreign keys:
Consider the following scenario where we have two tables: Customers and Orders.
- Create the Customers table:
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(100)
);
- Create the Orders table:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
In the Orders table, the customer_id column is a foreign key that references the primary key column customer_id in the Customers table.
This establishes a one-to-many relationship between the Customers and Orders tables. One customer can have multiple orders, but each order is associated with only one customer.
To insert data into the tables and establish the relationship, you can use the following SQL statements:
— Inserting a customer record
INSERT INTO Customers (customer_id, customer_name, customer_email)
VALUES (1, ‘John Doe’, ‘john.doe@example.com’);
— Inserting an order record associated with the customer
INSERT INTO Orders (order_id, order_date, customer_id)
VALUES (1, ‘2023-05-17’, 1);
In this example, we first insert a customer record into the Customers table with customer_id 1. Then, we insert an order record into the Orders table with order_id 1 and customer_id 1, referencing the customer record we just inserted.
By establishing foreign key relationships between tables, you can enforce referential integrity and ensure that data is consistent and accurately linked between related tables.