Как реализовать связь один к одному

Создание связи «один к одному»

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

Связь «один-к-одному» — это связь между информацией из двух таблиц, когда каждая запись используется в каждой таблице только один раз. Например, связь типа «один-к-одному» может использоваться между сотрудниками и их служебными автомобилями. Каждый работник указан в таблице «Сотрудники» только один раз, как и каждый автомобиль в таблице «Служебный транспорт».

Связи «один-к-одному» можно использовать, если у вас есть таблица со списком элементов, но конкретные сведения о них зависят от типа. Например, у вас может быть таблица контактов, в которой некоторые сотрудники являются сотрудниками, а другие — субподрядчиками. Для сотрудников нужно знать их номера, расширения и другие ключевые сведения. Для субподрядчиков нужно знать, помимо прочего, название компании, номер телефона и тариф на выставление счета. В этом случае нужно создать три отдельные таблицы — «Контакты», «Сотрудники» и «Субподрядчики», а затем создать связь «один-к-одному» между таблицами «Контакты» и «Сотрудники» и связь «один-к-одному» между таблицами «Контакты» и «Субподрядчики».

Общие сведения о создании связи «один к одному»

Связи «один-к-одному» создаются путем связывания индекса первой таблицы, в качестве которого обычно выступает первичны ключ, с индексом второй таблицы, причем их значения совпадают. Пример:

Часто бывает, что лучший способ создать подобную связь — назначить вторичной таблице функцию поиска значений из первой таблицы. Например, вы можете сделать поле «Код автомобиля» в таблице «Сотрудники» полем подстановки, которое будет искать значение индекса «Код автомобиля» в таблице «Служебный транспорт». Таким образом исключается случайное добавление кода автомобиля, который на самом деле не существует.

Важно: При создании связи «один-к-одному» следует тщательно обдумать, требуется ли включать для нее обеспечение целостности данных.

Целостность данных помогает Access поддерживать порядок данных путем удаления связанных записей. Например, при удалении сотрудника из таблицы «Сотрудники» также удаляются записи о его льготах из таблицы «Льготы». Но в некоторых связях, таких как в этом примере, целостность данных не имеет смысла: если удалить сотрудника, мы не хотим, чтобы автомобиль удалялся из таблицы «Автомобиль компании», так как он по-прежнему будет принадлежать компании и будет назначен другому сотруднику.

Инструкции по созданию связи типа «один к одному»

Вы можете создать связь «один-к-одному», добавив в таблицу поле подстановки. (Инструкции см. в статье Создание таблиц и назначение типов данных.) Например, чтобы указать, какие автомобили назначены определенным сотрудникам, вы можете добавить в таблицу «Сотрудники» поле «Код автомобиля». После этого воспользуйтесь мастером подстановок для создания связи между полями.

1. Откройте таблицу.
2. В режиме конструктора добавьте новое поле, выберите значение Тип данных, а затем запустите мастер подстановок.
3. В мастере по умолчанию выбран поиск значений в другой таблице, поэтому нажмите кнопку Далее.
4. Выберите таблицу с ключом (обычно первичным), который вы хотите добавить в первую таблицу, и нажмите кнопку Далее. В рассмотренном примере следует выбрать таблицу «Служебный транспорт».
5. Добавьте в список Выбранные поля поле с необходимым ключом. Нажмите кнопку Далее.
6. Задайте порядок сортировки и, при необходимости, измените ширину поля.
7. В последнем окне установите флажок Включить проверку целостности данных и нажмите кнопку Готово.

Как сделать связь между таблицами один к одному?

Задача искусственная. На практике такая связь почти никогда не используется. Разве что достигнуто ограничение на количество полей (атрибутов).

28 окт 2020 в 11:31

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Сделайте вот так:

create table a ( id number(5) primary key, name varchar2 (128)) / create table b ( id number(5) not null unique references a (id), name varchar2 (128)) / 

Ограничение unique обеспечит связь один-к-одному, а заодно, добавит индекс для внешнего ключа, который не создаётся автоматически.

Отслеживать
ответ дан 28 окт 2020 в 1:40
51.6k 201 201 золотой знак 63 63 серебряных знака 245 245 бронзовых знаков
Pasport И ID_Card это записи в Table
28 окт 2020 в 7:47
@TeonaNadiradze Извините, ожидал информацию по таблице, а не данные. Поправил.
28 окт 2020 в 11:12

• sql
• oracle

Важное на Мете

Похожие

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Дизайн сайта / логотип © 2024 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2024.1.3.2953

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Типы связей в реляционных базах данных

Примечание:
Во всех статьях текущей категории уроков по SQL используются примеры и задачи, основанные на учебной базе данных.

Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.

Практически всегда БД не ограничивается одной таблицей. Сложно представить себе какой-либо бизнес-процесс на предприятии, который мог бы сконцентрироваться только на одном предмете в плане информации.

Рассмотрим пример учебной базы данных. Имеется отдел, который занимается обработкой звонков, поступающих на различные линии. Линии обслуживаются конкретными операторами. Операторы состоят в разных группах под присмотром супервайзеров.

Только из данного краткого описания можно выделить несколько самостоятельных объектов:

• Телефонные линии обслуживания;
• Сотрудники отдела;
• Должности сотрудников;
• Группы, по которым распределены сотрудники;
• Звонки.

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

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

Логику соединения таблиц в БД важно понять с самого начала изучения SQL, так как наверняка Вы не будете писать запросы только к одной таблице.

Всего существует 3 типа связей:

Примечание:
В данном материале обозначения связей приводятся на примере MS SQL Server. В иных СУБД они могут обозначаться по-разному, но у Вас не должно возникнуть проблем с определением их типа, т.к. они либо очень похожи, либо интуитивно понятны.

Связь «Один к одному»

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

На практике связь «один к одному» наблюдается не часто. Например, она может возникнуть, когда требуется разделить данных одной таблицы на несколько отдельных таблиц с целью безопасности.

В учебной безе данных нет подходящего примера, но гипотетически могла бы существовать необходимость разделения таблицы сотрудников.

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

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

Связь «Один ко многим»

В типе связей один ко многим одной записи первой таблицы соответствует несколько записей в другой таблице.

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

Записи должностей в таблице «Должность» уникальны, так как нет смысла повторно создавать имеющуюся запись. Записи в таблице «Сотрудники» также уникальны, но несколько различных сотрудников могут находиться на одинаковой должностной позиции.

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

Связь «Многие ко многим»

Если нескольким записям из одной таблицы соответствует несколько записей из другой таблицы, то такая связь называется «многие ко многим» и организовывается посредством связывающей таблицы.

В нашей базе подобное наблюдается только между таблицами с сотрудниками и линиями.

Из диаграммы видно, что имеются две связи «один ко многим» (один сотрудник может обрабатывать несколько телефонных линий, и одну линию могут обрабатывать несколько сотрудников), но в совокупности они образуют связь «многие ко многим».

Для чего все это нужно?

Связи выполняют более важную роль, чем просто информация размещения данных по таблицам. Прежде всего они требуются разработчикам для поддержания целостности баз данных.

Правильно настроив связи, можно быть уверенным, что ничего не потеряется.

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

• Объединение таблиц – UNION
• Соединение таблиц – операция JOIN и ее виды
• Тест на знание основ SQL

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Связи между таблицами базы данных

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

1.1. Для кого эта статья?

Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.

1.2. Как вы можете применить эти знания?

1. Процесс создания баз данных станет для вас легче и понятнее.
2. Понимание связей между таблицами поможет вам легче освоить нормализацию, что является очень важным при проектировании базы данных.
3. Разобраться с чужой базой данных будет значительно проще.
4. На собеседовании это будет очень хорошим плюсом.

2. Благодарности

Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!

3.1. Как организовываются связи?

Связи создаются с помощью внешних ключей (foreign key).
Внешний ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

3.2. Виды связей

Связи делятся на:

1. Многие ко многим.
2. Один ко многим.
   • с обязательной связью;
   • с необязательной связью;
3. Один к одному.
   • с обязательной связью;
   • с необязательной связью;

4. Многие ко многим

Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:

• Работник может иметь одну и более должностей. Например, некий работник может быть и админом, и программистом.
• Должность может «владеть» одним и более работников. Например, админами является определенный набор работников. Другими словами, к админам относятся некие работники.

4.1. Как построить такие таблицы?

Мы уже имеем две таблицы, описывающие работника и профессию. Теперь нам нужно установить между ними связь многие ко многим. Для реализации такой связи нам нужен некий посредник между таблицами «Employee» и «Position». В нашем случае это будет некая таблица «EmployeesPositions» (работники и должности). Эта таблица-посредник связывает между собой работника и должность следующим образом:

EmployeeId	PositionId
1	1
1	2
2	3
3	3

Слева указаны работники (их id), справа — должности (их id). Работники и должности на этой таблице указываются с помощью id’шников.

На эту таблицу можно посмотреть с двух сторон:

1. Таким образом, мы говорим, что работник с id 1 находится на должность с id 1. При этом обратите внимание на то, что в этой таблице работник с id 1 имеет две должности: 1 и 2. Т.е., каждому работнику слева соответствует некая должность справа.
2. Мы также можем сказать, что должности с id 3 принадлежат пользователи с id 2 и 3. Т.е., каждой роли справа принадлежит некий работник слева.

4.2. Реализация

Диаграмма

Код на T-SQL

create table dbo.Employee ( EmployeeId int primary key, EmployeeName nvarchar(128) not null, EmployeeAge int not null ) -- Заполним таблицу Employee данными. insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (1, N'John Smith', 22) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (2, N'Hilary White', 22) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (3, N'Emily Brown', 22) create table dbo.Position ( PositionId int primary key, PositionName nvarchar(64) not null ) -- Заполним таблицу Position данными. insert into dbo.Position(PositionId, PositionName) values(1, N'IT-director') insert into dbo.Position(PositionId, PositionName) values(2, N'Programmer') insert into dbo.Position(PositionId, PositionName) values(3, N'Engineer') -- Заполним таблицу EmployeesPositions данными. create table dbo.EmployeesPositions ( PositionId int foreign key references dbo.Position(PositionId), EmployeeId int foreign key references dbo.Employee(EmployeeId), primary key(PositionId, EmployeeId) ) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 1) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 2) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (2, 3) insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (3, 3)

Объяснения

С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы

• ссылаемся атрибутом PositionId таблицы EmployeesPositions на атрибут PositionId таблицы Position;
• атрибутом EmployeeId таблицы EmployeesPositions — на атрибут EmployeeId таблицы Employee;

4.3. Вывод

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

5. Один ко многим

Эта самая распространенная связь между базами данных. Мы рассматриваем ее после связи многие ко многим для сравнения.

Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).

В этом случае мы наблюдаем следующее: пользователь может иметь многие номера телефонов, но нельзя сказать, что номеру телефона принадлежит определенный пользователь.

Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).

Как мы видим, это отношение один ко многим.

5.1. Как построить такие таблицы?

Пользователей будет представлять некая таблица «Person» (id, имя, фамилия, возраст), номера телефонов будет представлять таблица «Phone». Она будет выглядеть так:

PhoneId	PersonId	PhoneNumber
1	5	11 091-10
2	5	19 124-66
3	17	21 972-02

Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

5.2. Почему мы не делаем тут таблицу-посредника?

Таблица-посредник нужна только в том случае, если мы имеем связь многие-ко-многим. По той простой причине, что мы можем рассматривать ее с двух сторон. Как, например, таблицу EmployeesPositions ранее:

1. Каждому работнику принадлежат несколько должностей (многие).
2. Каждой должности принадлежит несколько работников (многие).

5.3. Реализация

Диаграмма

Код на T-SQL

create table dbo.Person ( PersonId int primary key, FirstName nvarchar(64) not null, LastName nvarchar(64) not null, PersonAge int not null ) insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (5, N'John', N'Doe', 25) insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (17, N'Izabella', N'MacMillan', 19) create table dbo.Phone ( PhoneId int primary key, PersonId int foreign key references dbo.Person(PersonId), PhoneNumber varchar(64) not null ) insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (1, 5, '11 091-10') insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (2, 5, '19 124-66') insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (3, 17, '21 972-02') 

Объяснения

Наша таблица Phone хранит всего один внешний ключ. Он ссылается на некого пользователя (на строку из таблицы Person). Таким образом, мы как бы говорим: «этот пользователь является владельцем данного телефона». Другими словами, телефон знает id своего владельца.

6. Один к одному

Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).

Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:

DisabledPersonId	EmployeeId
1	159
2	722
3	937

Но это еще не связь один к одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один ко многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.

Выполнив это мы получили связь один к одному.

Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.

6.1. Вывод

Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.

6.2. Реализация

Диаграмма

Код на T-SQL

create table dbo.Employee ( EmployeeId int primary key, EmployeeName nvarchar(128) not null, EmployeeAge int not null ) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (159, N'John Smith', 22) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (722, N'Hilary White', 29) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (937, N'Emily Brown', 19) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (100, N'Frederic Miller', 16) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (99, N'Henry Lorens', 20) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (189, N'Bob Red', 25) create table dbo.DisabledEmployee ( DisabledPersonId int primary key, EmployeeId int unique foreign key references dbo.Employee(EmployeeId) ) insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (1, 159) insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (2, 722) insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (3, 937)

Объяснения

Таблица DisabledEmployee имеет атрибут EmployeeId, что является внешним ключом. Он ссылается на атрибут EmployeeId таблицы Employee. Кроме того, этот атрибут имеет ограничение unique, что говорит о том, что в него могут быть записаны только уникальные значения. Соответственно, работник может быть записан в эту таблицу не более одного раза.

7. Обязательные и необязательные связи

Связи можно поделить на обязательные и необязательные.

7.1. Один ко многим

1. Один ко многим с обязательной связью:
   К одному полку относятся многие бойцы. Один боец относится только к одному полку. Обратите внимание, что любой солдат обязательно принадлежит к одному полку, а полк не может существовать без солдат.
2. Один ко многим с необязательной связью:
   На планете Земля живут все люди. Каждый человек живет только на Земле. При этом планета может существовать и без человечества. Соответственно, нахождение нас на Земле не является обязательным

У одной биологической матери может быть много детей. У ребенка есть только одна биологическая мать.

А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.

7.2. Один к одному

1. Один к одному с обязательной связью:
   У одного гражданина определенной страны обязательно есть только один паспорт этой страны. У одного паспорта есть только один владелец.
2. Один к одному с необязательной связью:
   У одной страны может быть только одна конституция. Одна конституция принадлежит только одной стране. Но конституция не является обязательной. У страны она может быть, а может и не быть, как, например, у Израиля и Великобритании.

У одного человека может быть только один загранпаспорт. У одного загранпаспорта есть только один владелец.

А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.

7.3. Многие ко многим

Любая связь многие ко многим является необязательной. Например:

Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).

А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.

8. Как читать диаграммы?

Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.

Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.

1. Возле таблицы Person находится золотой ключик. Он обозначает слово «один».
2. Возле таблицы Phone находится знак бесконечности. Он обозначает слово «многие».

9. Итоги

1. Связи бывают:
   • Многие ко многим.
   • Один ко многим.
     1) с обязательной связью;
     2) с необязательной связью.
   • Один к одному.
     1) с обязательной связью;
     2) с необязательной связью.
2. Связи организовываются с помощью внешних ключей.
3. Foreign key (внешний ключ) — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

10. Задачи

Для лучшего усвоения материала предлагаю вам решить следующие задачи:

1. Описать таблицу фильм: id, название, длительность, режиссер, жанр фильма. Обратите внимание на то, что у фильма может быть более одного жанра, а к одному жанру может относится более, чем один фильм.
2. Описать таблицу песня: id, название, длительность, певец. При этом у песни может быть более одного певца, а певец мог записать более одной песни.
3. Реализовать таблицу машина: модель, производитель, цвет, цена
   • Описать отдельную таблицу производитель: id, название, рейтинг.
   • Описать отдельную таблицу цвета: id, название.

   У одной машины может быть только один производитель, а у производителя — много машин. У одной машины может быть много цветов, а у одного цвета может быть много машин.

4. Добавить в БД из пункта 6.2. таблицу военно-обязанных по типу того, как мы описали отдельную таблицу DisabledEmployee.

• SQL
• Microsoft SQL Server