Связи между таблицами в Sql

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

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

Связи — это довольна важная тема, которую следует понимать при проектировании баз данных.

Связи между таблицами в базе данных в SQL представляют собой отношения между данными в различных таблицах, которые позволяют эффективно организовывать и структурировать информацию. Эти связи обеспечивают способ объединения данных из разных таблиц для выполнения запросов и получения комплексной информации.

Содержание

Связи обычно устанавливаются между таблицами посредством ключей, таких как первичные ключи и внешние ключи.

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

Один к одному (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 и ознакомьтесь с условиями обучения, мы специализируемся только на индивидуальных занятиях, как для начинающих, так и для более продвинутых программистов. Вы можете взять как одно занятие для проработки интересующего Вас вопроса, так и несколько, для более плотной работы. Благодаря личному кабинету, каждый студент повысит качество своего обучения, в вашем распоряжении: