Understanding Relationships between Tables.
In a SQL relational database table, table entities are related or linked to each other through relationships between table column fields. These table relationships help to manage, arrange and structure the data so that data and information queries can be created or extracted efficiently from more than one related table as per the requirement. Establishing relationships between table entities is a fundamental concept in database design in SQL tables. So that the database developer designer can retrieve efficient data and information queries as per the requirement.
Types of table relationships between tables in SQL tables.
- One-to-one (1-1) table relationship.
- One-to-many (1-M) table relationship.
- Many-to-many (M-M) table relationship.
In addition, in SQL tables, foreign keys and primary keys are used to define table record column relationships.
SQL one-to-one (1-1) table relationship.
A one to one (1-1) table relationship is created in SQL when a record in one table is related to an exact same table record in another table. For a one-to-one table relationship, at least one common column field must be present in both the tables. This table relationship is usually used when you need to store table data that is related to a table record.
SQL one-to-one relationship example.
Here you see a one-to-one table relationship between a Person table and a passport table. Where each person has only a single unique passport ID.
How a one-to-one table relationship works.
Where each row in the Person table must match exactly one row in the second table.
Where a primary key in the Person table is also used as a foreign key in the Passport table to enforce the table relationship.
Schema (1-1 relationship) example in SQL table.
CREATE TABLE persons (
persons_id INT PRIMARY KEY,
persons_name VARCHAR(70),
age INT
);
CREATE TABLE passport (
passport_id INT PRIMARY KEY,
person_id INT,
passport_number VARCHAR(70),
psssport_name VARCHAR(70),
FOREIGN KEY (person_id) REFERENCES person(person_id)
);
Here in this relationship table case above.
Where person_id in passport table is a foreign key that points to person_id in person table. This ensures you in table relationship that each passport is related to a specific individual person.
SQL one-to-many (1-M) table relationship.
One to many (1-M) table relationship is the most common table relation type in SQL table. One to many table relationship is created in SQL table when one record in one table is related to many table records in another table column field.
SQL one-to-many (1-M) table relationship example.
In SQL table there is a department table and an employee table where each department can have many employees. But each employee is related to only one department.
How one-to-many (1-M) table relationship works.
One-to-many (1-M) table relationship in SQL tables. A primary key in the first table is referenced as a foreign key in the second table.
SQL one-to-many (1-M) relationship schema example.
CREATE TABLE departments (
departments_id INT PRIMARY KEY,
departments_name VARCHAR(100),
departments_contact int
);
CREATE TABLE employe (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
employe_contact int,
department_id INT,
FOREIGN KEY (department_id) REFERENCES department(department_id)
);
one-to-many (1:M) relationship in this case.
One-to-many in SQL tables. Each employee is related to a department in the employee table through the department_id foreign key.
Where a single record in the SQL department table can be related to multiple records in the employee table.
SQL many-to-many (M-M) table relationship.
A many-to-many (M-M) table entity relationship in SQL tables is created when multiple records in one SQL table are related to multiple records in the second table. This is usually managed using a junction table (also known as a bridge or associative table). So that the table relation can be divided into two one-to-many relationships as per the requirement.
many-to-many (M-M) table relationship example.
Many to many in SQL table is a student table and a course table, where each student can enroll in many courses, and each course can have many students related to it.
How a many-to-many (M-M) table relationship works.
Many to many junction table has foreign keys. Which references the primary keys of both the related tables.
many-to-many (M-M) table example schema (M-M relationship).
CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(100),
student_rolno int
);
CREATE TABLE course (
course_id INT PRIMARY KEY,
course_name VARCHAR(80),
course_medium VARCHAR(90),
);
CREATE TABLE student_course (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (course_id) REFERENCES course(course_id)
);
Here in this example.
Here student_course table is a junction table, which relates students and courses.
Here a many to many relationship exists between student and course tables through student_course table. Where each student can enroll in multiple courses, and each course can have multiple students.
Practical example with data in SQL tables.
Inserting data and information into tables with relationships in existing SQL tables.
So let’s insert some basic data and information into multiple SQL tables Department and Employee tables, where the tables have a one-to-many table relationship.
Insert data and information into SQL Department table.
INSERT INTO employee (employee_id, name, department_id)
VALUES (01, ‘Harry’, 1001),
(02, ‘David’, 1003),
(03, ‘Sid’, 1007),
(04, ‘Bhavishi’, 1009);
Insert data and information into SQL Employee table.
Here above, we have inserted department field in Department table, e.g., Human Resources, Engineering and Marketing are department record information.
Where we have inserted four employees in Employee table, out of which some of the employees are appointed in the same department.
Relationships in existing SQL tables It is important to understand the relationships between tables in a SQL table in order to design a database in an effective order and to query database tables.
There are three main types of table relationships in any SQL table.
- One-to-one (1-1) relationships in SQL tables – Each record in one SQL table must correspond to exactly one record in a second table.
- One-to-many (1-M) relationships in SQL tables – In a SQL table, one record in the first table must be related to many records in the second table.
- Many-to-many (M-M) relationships in SQL tables – In a SQL table, many records in one table are related to many records in another table, usually through a junction table.
