Зв'язки між таблицями в Sql - Dijix

[ Полезный рекламный блок ]

Попробуйте свои силы в игре, где ваши навыки программирования на C# станут решающим фактором. Переходите по ссылке 🔰.

Зв’язки – це доволі важлива тема, яку слід розуміти під час проектування баз даних.

Зв’язки між таблицями в базі даних у SQL являють собою відносини між даними в різних таблицях, які дають змогу ефективно організовувати та структурувати інформацію. Ці зв’язки забезпечують спосіб об’єднання даних з різних таблиць для виконання запитів і отримання комплексної інформації.

Table of Contents

Зв’язки зазвичай встановлюються між таблицями за допомогою ключів, таких як первинні ключі та зовнішні ключі.

Основные типы связей

Один до одного (One-to-One): Кожен запис в одній таблиці пов’язаний з одним і тільки одним записом в іншій таблиці.
Один до багатьох (One-to-Many): Кожен запис в одній таблиці може бути пов’язаний з кількома записами в іншій таблиці. Це найпоширеніший тип зв’язку.
Багато до одного (Many-to-One): Кілька записів в одній таблиці пов’язані з одним записом в іншій таблиці.

Зв’язки “один до багатьох” (One-to-Many) і “багато хто до одного” (Many-to-One) являють собою два боки одного й того самого зв’язку. Термінологія може відрізнятися залежно від того, з якого боку зв’язку ви дивитеся на нього.

Зв’язок “один до багатьох” означає, що один запис з однієї таблиці може бути пов’язаним із кількома записами з іншої таблиці. Приклад: один автор може мати кілька книг.
Зв’язок “багато до одного” означає, що кілька записів з однієї таблиці можуть бути пов’язані з одним записом з іншої таблиці. Приклад: кілька книг можуть бути написані одним автором.

Суть у тому, що це одна й та сама концепція, але вона може бути виражена різними словами залежно від того, на який бік зв’язку ви звертаєте увагу.

Багато до багатьох (Many-to-Many): Безліч записів в одній таблиці пов’язано з безліччю записів в іншій таблиці. Для реалізації такого зв’язку потрібна додаткова таблиця (часто звана проміжною або зведеною), яка встановлює відповідність між записами обох таблиць.

Зв’язки дають змогу ефективно організовувати дані й уникати надмірності та неузгодженості. Вони також забезпечують цілісність даних, оскільки можуть обмежувати дії, які можуть порушити цілісність пов’язаних даних.

При створенні таблиць зі зв’язками в SQL зазвичай використовуються ключі: первинні ключі (Primary Key) для ідентифікації унікальних записів у таблиці та зовнішні ключі (Foreign Key), які створюють зв’язок із записами в іншій таблиці, використовуючи значення первинних ключів. Зв’язки визначаються під час створення таблиць за допомогою спеціальних операторів і обмежень, таких як FOREIGN KEY.

Розглянемо всі 3 основні зв’язки детальніше.

Один до одного

Зв’язок “один до одного” (One-to-One) у SQL означає, що кожен запис в одній таблиці пов’язаний з одним і тільки одним записом в іншій таблиці. Це може бути корисним, наприклад, коли у вас є дві сутності, які мають різні аспекти або додаткові властивості, які ви хочете зберігати в окремих таблицях.

Припустимо, у нас є дві сутності: “[Users]” і “[UserSettings]”. У кожного користувача може бути тільки один запис з інформацією, і кожен запис з інформацією також може бути пов’язаний тільки з одним користувачем:

create table [Users]
(
[Id] INT IDENTITY PRIMARY KEY,
[Email] NVARCHAR(200) NOT NULL,
[Name] NVARCHAR(60) NOT NULL,
[Age] INT NULL
)

INSERT INTO [Users] ([Email], [Name], [Age])
VALUES
    ('john.doe@example.com', 'John Doe', 28),
    ('jane.smith@example.com', 'Jane Smith', 35),
    ('michael.johnson@example.com', 'Michael Johnson', 42),
    ('emily.wilson@example.com', 'Emily Wilson', NULL),
    ('william.davis@example.com', 'William Davis', 22),
    ('olivia.brown@example.com', 'Olivia Brown', 31),
    ('james.anderson@example.com', 'James Anderson', NULL),
    ('sophia.miller@example.com', 'Sophia Miller', 19),
    ('alexander.wilson@example.com', 'Alexander Wilson', 39),
    ('ava.jones@example.com', 'Ava Jones', 27);
create table [UserSettings]
(
[Id] INT IDENTITY PRIMARY KEY,
[UserId] INT UNIQUE NOT NULL,
[Country] NVARCHAR(200),
[City] NVARCHAR(200))


INSERT INTO [UserSettings] ([UserId], [Country], [City])
VALUES (1, 'Usa', 'Chicago'), (4, 'China', 'Hong Kong'), (7, 'Usa', 'New York'), (8, 'Japan', 'Tokio')

create table [Users]

(

[Id] INT IDENTITY PRIMARY KEY,

[Email] NVARCHAR(200) NOT NULL,

[Name] NVARCHAR(60) NOT NULL,

[Age] INT NULL

)

INSERT INTO [Users] ([Email], [Name], [Age])

VALUES

('john.doe@example.com', 'John Doe', 28),

('jane.smith@example.com', 'Jane Smith', 35),

('michael.johnson@example.com', 'Michael Johnson', 42),

('emily.wilson@example.com', 'Emily Wilson', NULL),

('william.davis@example.com', 'William Davis', 22),

('olivia.brown@example.com', 'Olivia Brown', 31),

('james.anderson@example.com', 'James Anderson', NULL),

('sophia.miller@example.com', 'Sophia Miller', 19),

('alexander.wilson@example.com', 'Alexander Wilson', 39),

('ava.jones@example.com', 'Ava Jones', 27);

create table [UserSettings]

(

[Id] INT IDENTITY PRIMARY KEY,

[UserId] INT UNIQUE NOT NULL,

[Country] NVARCHAR(200),

[City] NVARCHAR(200))

INSERT INTO [UserSettings] ([UserId], [Country], [City])

VALUES (1, 'Usa', 'Chicago'), (4, 'China', 'Hong Kong'), (7, 'Usa', 'New York'), (8, 'Japan', 'Tokio')

Отримаємо пов’язані дані з двох таблиць:

select * from [Users]
LEFT JOIN [UserSettings] on [Users].[Id] = [UserSettings].[UserId]

1 2	select * from [Users] LEFT JOIN [UserSettings] on [Users].[Id] = [UserSettings].[UserId]

Таким чином, у нас утворюється зв’язок “один до одного” між цими двома таблицями.

Один до багатьох

Зв’язок “один до багатьох” (One-to-Many) у SQL означає, що кожен запис в одній таблиці може бути пов’язаним із кількома записами в іншій таблиці. Цей тип зв’язку широко використовується для організації даних, де в однієї сутності є безліч пов’язаних елементів іншої сутності.

Припустимо, у нас є дві сутності: “[Users]” і “[AncestralHome]”. Кожен рідний дім може містити кілька користувачів, але кожен користувач належить тільки одному рідному дому:

create table [AncestralHome]
(
[Id] INT IDENTITY PRIMARY KEY,
[Name] NVARCHAR(200),
[Country] NVARCHAR(200),
[Address] NVARCHAR(200)
)

INSERT INTO [AncestralHome] ([Name], [Country], [Address])
VALUES
    ('Smith Residence', 'United States', '123 Main St, Springfield'),
    ('Johnson Manor', 'Canada', '456 Maple Ave, Toronto'),
    ('Garcia Homestead', 'Mexico', '789 Calle Principal, Mexico City');

create table [Users]
(
[Id] INT IDENTITY PRIMARY KEY,
[Email] NVARCHAR(200) NOT NULL,
[Name] NVARCHAR(60) NOT NULL,
[Age] INT NULL,
[AncestralHomeId] INT NOT NULL,
FOREIGN KEY ([AncestralHomeId]) REFERENCES [AncestralHome]([Id])
)

INSERT INTO [Users] ([Email], [Name], [Age], [AncestralHomeId])
VALUES
    ('john.doe@example.com', 'John Doe', 28, 1),
    ('jane.smith@example.com', 'Jane Smith', 35, 3),
    ('michael.johnson@example.com', 'Michael Johnson', 42, 1),
    ('emily.wilson@example.com', 'Emily Wilson', NULL, 1),
    ('william.davis@example.com', 'William Davis', 22, 2),
    ('olivia.brown@example.com', 'Olivia Brown', 31, 3),
    ('james.anderson@example.com', 'James Anderson', NULL, 3),
    ('sophia.miller@example.com', 'Sophia Miller', 19, 1),
    ('alexander.wilson@example.com', 'Alexander Wilson', 39, 2),
    ('ava.jones@example.com', 'Ava Jones', 27, 2);

create table [AncestralHome]

(

[Id] INT IDENTITY PRIMARY KEY,

[Name] NVARCHAR(200),

[Country] NVARCHAR(200),

[Address] NVARCHAR(200)

)

INSERT INTO [AncestralHome] ([Name], [Country], [Address])

VALUES

('Smith Residence', 'United States', '123 Main St, Springfield'),

('Johnson Manor', 'Canada', '456 Maple Ave, Toronto'),

('Garcia Homestead', 'Mexico', '789 Calle Principal, Mexico City');

create table [Users]

(

[Id] INT IDENTITY PRIMARY KEY,

[Email] NVARCHAR(200) NOT NULL,

[Name] NVARCHAR(60) NOT NULL,

[Age] INT NULL,

[AncestralHomeId] INT NOT NULL,

FOREIGN KEY ([AncestralHomeId]) REFERENCES [AncestralHome]([Id])

)

INSERT INTO [Users] ([Email], [Name], [Age], [AncestralHomeId])

VALUES

('john.doe@example.com', 'John Doe', 28, 1),

('jane.smith@example.com', 'Jane Smith', 35, 3),

('michael.johnson@example.com', 'Michael Johnson', 42, 1),

('emily.wilson@example.com', 'Emily Wilson', NULL, 1),

('william.davis@example.com', 'William Davis', 22, 2),

('olivia.brown@example.com', 'Olivia Brown', 31, 3),

('james.anderson@example.com', 'James Anderson', NULL, 3),

('sophia.miller@example.com', 'Sophia Miller', 19, 1),

('alexander.wilson@example.com', 'Alexander Wilson', 39, 2),

('ava.jones@example.com', 'Ava Jones', 27, 2);

Отримаємо користувачів та їхні батьківські будинки:

select * from [Users]
JOIN [AncestralHome] on [Users].[AncestralHomeId] = [AncestralHome].[Id]

1 2	select * from [Users] JOIN [AncestralHome] on [Users].[AncestralHomeId] = [AncestralHome].[Id]

А тепер, отримаємо всіх користувачів, які народилися в батьківському домі номер 1:

select * from [AncestralHome]
LEFT JOIN [Users] on  [AncestralHome].[Id] = [Users].[AncestralHomeId]
WHERE [AncestralHome].[Id] = 1

select * from [AncestralHome]

LEFT JOIN [Users] on [AncestralHome].[Id] = [Users].[AncestralHomeId]

WHERE [AncestralHome].[Id] = 1

Таким чином, кожен запис у таблиці “[Users]” може бути пов’язаний з конкретним будинком із таблиці “[AncestralHome]”. Це і є типовий приклад зв’язку “один до багатьох” у SQL.

Багато до багатьох

Зв’язок “багато до багатьох” (Many-to-Many) у SQL означає, що багато записів в одній таблиці може бути пов’язано з безліччю записів в іншій таблиці. Такі зв’язки виникають, коли у вас є ситуація, коли кожен запис в одній таблиці може бути пов’язаним із кількома записами в іншій таблиці, і навпаки.

Припустимо, у нас є дві сутності: “[Users]” і “[Services]”. Один користувач може бути підписаний на кілька сервісів, і кожен сервіс може мати кілька користувачів.

Для реалізації зв’язку “багато до багатьох” зазвичай використовують третю таблицю, звану таблицею зв’язку, яка зберігає пари ідентифікаторів з обох таблиць. У цій таблиці зв’язку унікальним чином комбінуються ідентифікатори записів із кожної зі зв’язаних таблиць.

create table [Users]
(
[Id] INT IDENTITY PRIMARY KEY,
[Email] NVARCHAR(200) NOT NULL,
[Name] NVARCHAR(60) NOT NULL,
[Age] INT NULL
)

INSERT INTO [Users] ([Email], [Name], [Age])
VALUES
    ('john.doe@example.com', 'John Doe', 28),
    ('jane.smith@example.com', 'Jane Smith', 35),
    ('michael.johnson@example.com', 'Michael Johnson', 42),
    ('emily.wilson@example.com', 'Emily Wilson', NULL),
    ('william.davis@example.com', 'William Davis', 22),
    ('olivia.brown@example.com', 'Olivia Brown', 31),
    ('james.anderson@example.com', 'James Anderson', NULL),
    ('sophia.miller@example.com', 'Sophia Miller', 19),
    ('alexander.wilson@example.com', 'Alexander Wilson', 39),
    ('ava.jones@example.com', 'Ava Jones', 27);
create table [Services]
(
[Id] INT IDENTITY PRIMARY KEY,
[Name] NVARCHAR(200),
[Description] NVARCHAR(MAX),
[Price] MONEY
)

INSERT INTO [Services] ([Name], [Description], [Price])
VALUES
    ('Netflix', 'Stream movies and TV shows online.', 9.99),
    ('Spotify', 'Listen to music and podcasts.', 4.99),
    ('Amazon Prime Video', 'Watch movies, TV shows, and originals.', 12.99),
    ('Disney+', 'Access Disney movies and TV shows.', 7.99),
    ('Hulu', 'Stream TV episodes and movies.', 5.99);
create table [UserService]
(
[UserId] INT NOT NULL,
[ServiceId] INT NOT NULL,
CONSTRAINT PK_User_Service PRIMARY KEY ([UserId],[ServiceId]),
FOREIGN KEY ([UserId]) REFERENCES [Users]([Id]),
FOREIGN KEY ([ServiceId]) REFERENCES [Services]([Id])
)

insert into [UserService] ([UserId], [ServiceId]) values (1,1),(1,2),(1,3),(2,4),(5,1),(5,2),(5,3)

create table [Users]

(

[Id] INT IDENTITY PRIMARY KEY,

[Email] NVARCHAR(200) NOT NULL,

[Name] NVARCHAR(60) NOT NULL,

[Age] INT NULL

)

INSERT INTO [Users] ([Email], [Name], [Age])

VALUES

('john.doe@example.com', 'John Doe', 28),

('jane.smith@example.com', 'Jane Smith', 35),

('michael.johnson@example.com', 'Michael Johnson', 42),

('emily.wilson@example.com', 'Emily Wilson', NULL),

('william.davis@example.com', 'William Davis', 22),

('olivia.brown@example.com', 'Olivia Brown', 31),

('james.anderson@example.com', 'James Anderson', NULL),

('sophia.miller@example.com', 'Sophia Miller', 19),

('alexander.wilson@example.com', 'Alexander Wilson', 39),

('ava.jones@example.com', 'Ava Jones', 27);

create table [Services]

(

[Id] INT IDENTITY PRIMARY KEY,

[Name] NVARCHAR(200),

[Description] NVARCHAR(MAX),

[Price] MONEY

)

INSERT INTO [Services] ([Name], [Description], [Price])

VALUES

('Netflix', 'Stream movies and TV shows online.', 9.99),

('Spotify', 'Listen to music and podcasts.', 4.99),

('Amazon Prime Video', 'Watch movies, TV shows, and originals.', 12.99),

('Disney+', 'Access Disney movies and TV shows.', 7.99),

('Hulu', 'Stream TV episodes and movies.', 5.99);

create table [UserService]

(

[UserId] INT NOT NULL,

[ServiceId] INT NOT NULL,

CONSTRAINT PK_User_Service PRIMARY KEY ([UserId],[ServiceId]),

FOREIGN KEY ([UserId]) REFERENCES [Users]([Id]),

FOREIGN KEY ([ServiceId]) REFERENCES [Services]([Id])

)

insert into [UserService] ([UserId], [ServiceId]) values (1,1),(1,2),(1,3),(2,4),(5,1),(5,2),(5,3)

Таблиця з назвою [UserService] є таблицею зв’язку, яка слугує для встановлення зв’язку “багато до багатьох” між двома іншими таблицями: [Users] і [Services]. Вона дає змогу записати, які користувачі використовують які сервіси, і забезпечує цілісність даних за допомогою обмежень первинних і зовнішніх ключів. Ця таблиця являє собою типовий приклад для організації зв’язку “багато до багатьох” у реляційній базі даних.

Отримаємо всі сервіси, на які підписаний користувач з ім’ям “John Doe“:

select [Services].[Name], [Services].[Description] from [Users] 
JOIN [UserService] on [UserService].[UserId] = [Users].[Id]
JOIN [Services] on [UserService].[ServiceId] = [Services].[Id]
WHERE [Users].[Name] = 'John Doe'

select [Services].[Name], [Services].[Description] from [Users]

JOIN [UserService] on [UserService].[UserId] = [Users].[Id]

JOIN [Services] on [UserService].[ServiceId] = [Services].[Id]

WHERE [Users].[Name] = 'John Doe'

Отримаємо всіх передплатників сервісу “Netflix”:

select [Users].[Id], [Users].[Email], [Users].[Name] from [Services]
JOIN [UserService] on [UserService].[ServiceId] = [Services].[Id]
JOIN [Users] on [Users].[Id] = [UserService].[UserId]
WHERE [Services].[Name] = 'Netflix'

select [Users].[Id], [Users].[Email], [Users].[Name] from [Services]

JOIN [UserService] on [UserService].[ServiceId] = [Services].[Id]

JOIN [Users] on [Users].[Id] = [UserService].[UserId]

WHERE [Services].[Name] = 'Netflix'

Я сподіваюся, що вам сподобалося читати цю статтю, і вона виявилася легкою для розуміння. Будь ласка, дайте мені знати, якщо у вас є якісь коментарі або виправлення.

Так само вам може бути цікава попередня стаття – Як влаштований Dictionary у C#.

Ви хочете навчитися писати код мовою програмування C#?

Створювати різні інформаційні системи, що складаються з сайтів, мобільних клієнтів, десктопних додатків, телеграм-ботів тощо.

Переходьте до нас на сторінку Dijix і ознайомтеся з умовами навчання, ми спеціалізуємося тільки на індивідуальних заняттях, як для початківців, так і для просунутих програмістів. Ви можете взяти як одне заняття для опрацювання питання, що вас цікавить, так і кілька, для більш щільної роботи. Завдяки особистому кабінету, кожен студент підвищить якість свого навчання, у вашому розпорядженні: