что значит связь один к одному
Создание связи «один к одному»
Проверьте, как это работает!
Что такое связь «один к одному»?
Связи «один к одному» часто используются для получения важных данных, необходимых для ведения бизнеса.
Связь «один-к-одному» — это связь между информацией из двух таблиц, когда каждая запись используется в каждой таблице только один раз. Например, связь типа «один-к-одному» может использоваться между сотрудниками и их служебными автомобилями. Каждый работник указан в таблице «Сотрудники» только один раз, как и каждый автомобиль в таблице «Служебный транспорт».
Связи «один-к-одному» можно использовать, если у вас есть таблица со списком элементов, но конкретные сведения о них зависят от типа. Например, у вас может быть таблица контактов, в которой некоторые сотрудники являются сотрудниками, а другие — субподрядчиками. Для сотрудников нужно знать их номера, расширения и другие ключевые сведения. Для субподрядчиков нужно знать, помимо прочего, название компании, номер телефона и тариф на выставление счета. В этом случае нужно создать три отдельные таблицы — «Контакты», «Сотрудники» и «Субподрядчики», а затем создать связь «один-к-одному» между таблицами «Контакты» и «Сотрудники» и связь «один-к-одному» между таблицами «Контакты» и «Субподрядчики».
Общие сведения о создании связи «один к одному»
Связи «один-к-одному» создаются путем связывания индекса первой таблицы, в качестве которого обычно выступает первичны ключ, с индексом второй таблицы, причем их значения совпадают. Пример:
Часто бывает, что лучший способ создать подобную связь — назначить вторичной таблице функцию поиска значений из первой таблицы. Например, вы можете сделать поле «Код автомобиля» в таблице «Сотрудники» полем подстановки, которое будет искать значение индекса «Код автомобиля» в таблице «Служебный транспорт». Таким образом исключается случайное добавление кода автомобиля, который на самом деле не существует.
Важно: При создании связи «один-к-одному» следует тщательно обдумать, требуется ли включать для нее обеспечение целостности данных.
Целостность данных помогает Access поддерживать порядок данных путем удаления связанных записей. Например, при удалении сотрудника из таблицы «Сотрудники» также удаляются записи о его льготах из таблицы «Льготы». Но в некоторых связях, таких как в этом примере, целостность данных не имеет смысла: если удалить сотрудника, мы не хотим, чтобы автомобиль удалялся из таблицы «Автомобиль компании», так как он по-прежнему будет принадлежать компании и будет назначен другому сотруднику.
Инструкции по созданию связи типа «один к одному»
Вы можете создать связь «один-к-одному», добавив в таблицу поле подстановки. (Инструкции см. в статье Создание таблиц и назначение типов данных.) Например, чтобы указать, какие автомобили назначены определенным сотрудникам, вы можете добавить в таблицу «Сотрудники» поле «Код автомобиля». После этого воспользуйтесь мастером подстановок для создания связи между полями.
В режиме конструктора добавьте новое поле, выберите значение Тип данных, а затем запустите мастер подстановок.
В мастере по умолчанию выбран поиск значений в другой таблице, поэтому нажмите кнопку Далее.
Выберите таблицу с ключом (обычно первичным), который вы хотите добавить в первую таблицу, и нажмите кнопку Далее. В рассмотренном примере следует выбрать таблицу «Служебный транспорт».
Добавьте в список Выбранные поля поле с необходимым ключом. Нажмите кнопку Далее.
Задайте порядок сортировки и, при необходимости, измените ширину поля.
В последнем окне установите флажок Включить проверку целостности данных и нажмите кнопку Готово.
Руководство по связям типа «один к одному»
В этой статье описаны средства моделирования данных, работающие с Power BI Desktop. Здесь предоставлены рекомендации по работе со связями типа «один к одному». Связь типа «один к одному» можно создать, если обе таблицы содержат столбец общих и уникальных значений.
Общие сведения о связях в моделях в этой статье не приводятся. Если у вас есть пробелы в знаниях о связях, их свойствах или настройке, рекомендуем сначала прочитать статью Связи модели в Power BI Desktop.
Вы также должны иметь представление о проектировании схемы типа «звезда». Дополнительные сведения см. в статье Общие сведения о схеме типа «звезда» и ее значении в Power BI.
Существует два сценария, в которых задействованы связи типа «один к одному».
Диапазоны данных строк в таблицах. Одна бизнес-сущность или субъект загружаются в виде двух (или более) таблиц модели, возможно, потому, что их данные поступают из различных хранилищ данных. Этот сценарий может быть общим для таблиц типа аналитики. Например, основные сведения о продукте хранятся в операционной системе продаж, а дополнительные — в другом источнике.
Однако в большинстве случаев вы не связываете две таблицы типа факта связью «один к одному». Это обусловлено тем, что обе таблицы типа факта должны иметь одинаковую размерность и гранулярность. Кроме того, каждая таблица типа факта может потребовать уникальные столбцы, чтобы разрешить создание связи модели.
Вырожденные измерения
Если столбцы из таблицы типа факта используются для фильтрации или группирования, их можно сделать доступными в отдельной таблице. Таким образом вы отделяете столбцы, используемые для фильтрации или группирования, от столбцов, которые используются для суммирования строк фактов. Это разделение может:
Рассмотрим исходную таблицу продаж, в которой в двух столбцах хранятся сведения о заказах на продажу.
Столбец OrderNumber сохраняет номер заказа, а столбец OrderLineNumber — последовательность строк в указанном порядке.
Обратите внимание, что на следующей схеме модели столбцы номера заказа и номера строки заказа не были загружены в таблицу Продажи. Вместо этого их значения использованы для создания столбца суррогатного ключа с именем SalesOrderLineID. (Значение ключа вычисляется путем умножения номера заказа на 1000, а затем добавления номера строки заказа.)
Таблица Заказ на продажу предоставляет широкие возможности для авторов отчетов и имеет три столбца: Заказ на продажу, Строка заказа на продажу и Номер строки Она также включает в себя иерархию. Эти ресурсы таблиц поддерживают проекты отчетов, которые должны фильтровать, группировать или детализировать сведения по заказам и строкам заказов.
Так как таблица Заказ на продажу построена на основе данных о продажах, в каждой таблице должно быть точно одинаковое количество строк. Кроме того, в каждом столбце SalesOrderLineID должны содержаться совпадающие значения.
Диапазоны данных строк в таблицах
Рассмотрим пример, включающий две таблицы типа измерения со связью «один к одному»: Продукт и Категория продукта. Каждая таблица представляет импортированные данные и содержит столбец SKU (единица складского хранения), содержащий уникальные значения.
Ниже приведена диаграмма частичной модели двух таблиц.
Первая таблица называется Продукт и содержит три столбца: Цвет, Продукт и номер SKU. Первая таблица называется Категория продукта и содержит два столбца: Категория и SKU. Связь типа «один к одному» связывает два столбца SKU. Связь выполняет фильтрацию в обоих направлениях, которые всегда относятся к связям типа «один к одному».
Для демонстрации того, как работает распространение фильтра связей, на схеме модели показаны строки таблиц. Все примеры в этой статье основаны на этих данных.
Строки таблиц невозможно отобразить на схеме модели в Power BI Desktop. В этой статье это было сделано для наглядности.
В приведенном ниже списке описываются строки каждой из двух таблиц.
Обратите внимание, что таблица Категория продукта не содержит строку номера SKU продукта — CL-02. Далее в этой статье мы обсудим последствия этой отсутствующей строки.
В области Поля авторы отчетов могут найти поля, связанные с продуктом, в двух таблицах: Продукт и Категория продукта.
Посмотрим, что происходит при добавлении полей из обеих таблиц в визуальный элемент таблицы. В данном примере столбец SKU предоставляется из таблицы Продукт.
Обратите внимание, что значение Категория для продукта с номером SKU CL-02 — ПУСТОЕ. Это обусловлено тем, что в таблице Категория продукта нет строки для этого продукта.
Рекомендации
Если данные строк распределяются между таблицами моделей, мы рекомендуем избегать создания связей типа «один к одному» для одной и той же модели по мере возможности. Это обусловлено тем, что эта схема может:
Конкретные рекомендации различаются в зависимости от того, установлена ли связь «один к одному» внутри исходной группы или между ними. Дополнительные сведения об оценке отношений см. в разделе Вычисление связей.
Связь «один к одному» внутри исходной группы
Если между таблицами существует внутренняя связь «один к одному», рекомендуется объединить данные в одну таблицу модели. Это можно сделать путем слияния запросов Power Query.
Следующие шаги представляют методологию для консолидации и моделирования взаимосвязанных данных «один к одному».
Слияние запросов. При объединении двух запросов следует учитывать полноту данных в каждом запросе. Если один запрос содержит полный набор строк (например, основной список), объедините с ним другой запрос. Настройте преобразование слияния на использование левого внешнего соединения, которое является типом объединения по умолчанию. Этот тип объединения гарантирует, что все строки первого запроса будут дополняться всеми совпадающими строками второго. Разверните все необходимые столбцы второго запроса в первом запросе.
Отключить загрузку запроса. Не забудьте отключить загрузку второго запроса. Таким образом, он не будет загружать результат в виде таблицы модели. Такая конфигурация уменьшает размер хранилища модели данных и помогает разгружать область Поля.
В нашем примере авторы отчета теперь находят единственную таблицу с именем Продукт в области Поля. Она содержит все поля, связанные с продуктом.
Замена отсутствующих значений. Если второй запрос содержит несопоставимые строки, в столбцах, введенных из него, будут отображаться значения NULL. При необходимости рассмотрите возможность замены значений NULL на значение токена. Замена отсутствующих значений особенно важна, когда авторы отчетов фильтруют или группируют по значениям столбцов, так как в визуальных элементах отчета могут отображаться пустые поля.
Создание иерархий. Если существуют связи между столбцами консолидированной таблицы, рассмотрите возможность создания иерархий. Таким образом, авторы отчетов быстро определяют возможности для детализации визуального отчета.
В нашем примере авторы отчетов теперь могут использовать иерархию с двумя уровнями: Категория и Продукт.
Даже если вам нравится как отдельные таблицы помогают организовать поля, мы все же рекомендуем консолидировать их в единую таблицу. Вы по-прежнему сможете упорядочивать поля, однако уже с помощью папок отображения.
В нашем примере авторы отчетов могут найти поле Категория в папке отображения Маркетинг.
Если вы все же решите определить внутренние связи «один к одному» между исходными группами в вашей модели, по возможности убедитесь, что в соответствующих таблицах есть совпадающие строки. Так как внутренняя связь «один к одному» оценивается как обычная связь, в визуальных элементах ваших отчетов проблемы целостности данных могут отображаться в визуальном виде в вашем отчете как пустые значения. (Пример пустого значения группирования можно увидеть в визуальном элементе первой таблицы, представленной в этой статье.)
Связь «один к одному» между исходными группами
Если между таблицами существует межэлементная связь типа «один к одному», нет никакой альтернативной модели, кроме выполнения предварительной консолидации данных в источниках данных. Power BI будет оценивать связь модели «один к одному» как ограниченную связь. Поэтому следите за тем, чтобы в связанных таблицах были совпадающие строки, поскольку несовпадающие строки будут исключены из результатов запроса.
Посмотрим, что происходит, когда поля из обеих таблиц добавляются в визуальный элемент таблицы и между таблицами существует ограниченная связь.
Таблица отображает только две строки. Номер SKU CL-02 для продукта отсутствует, поскольку в таблице Категория продукта нет соответствующей строки.
Дальнейшие действия
Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.
Как определить связи между таблицами в базе данных Access
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Оригинальный номер КБ: 304466
Аннотация
В этой статье описывается, как определить отношения в базе данных Microsoft Access. Статья включает в себя следующие темы:
Что такое связи между таблицами?
В реляционной базе данных отношения позволяют предотвратить избыточные данные. Например, при разработке базы данных, которая будет отслеживать информацию о книгах, может быть таблица «Названия», в которой хранится информация о каждой книге, например название книги, дата публикации и издатель. Существует также информация, которую вы можете хранить об издателе, например, номер телефона издателя, адрес и почтовый индекс. Если вы храните всю эту информацию в таблице «Названия», номер телефона издателя будет дублироваться для каждого названия, которое печатает издатель.
Лучшим решением является хранение информации издателя только один раз, в отдельной таблице, которую мы будем называть «Издатели». Затем вы поместите указатель в таблице «Названия», которая ссылается на запись в таблице «Издатели».
Чтобы убедиться, что данные остаются синхронизированными, можно обеспечить целостность данных между таблицами. Отношения целостности данных помогают убедиться, что информация в одной таблице соответствует информации в другой. Например, каждое название в таблице «Названия» должно быть связано с конкретным издателем в таблице «Издатели». Название не может быть добавлено в базу данных для издателя, которого не существует в базе данных.
Логические связи в базе данных позволяют эффективно запрашивать данные и создавать отчеты.
Виды связей между таблицами
Связь работает путем сопоставления данных в ключевых столбцах, обычно столбцах (или полях), которые имеют одно и то же имя в обеих таблицах. В большинстве случаев связь соединяет основной ключ или уникальный столбец идентификатора для каждой строки, от одной таблицы к полю в другой таблице. Колонка в другой таблице называется «внешний ключ». Например, если вы хотите отслеживать продажи каждого названия книги, создайте связь между основным ключевым столбцом (назовем его title_ID) в таблице «Названия» и столбцом в таблице «Продажи», который называется title_ID. Столбец title_ID в таблице «Продажи» является внешним ключом.
Существует три вида связей между таблицами. Тип создаваемых связей зависит от того, как определяются связанные столбцы.
Связи «один ко многим»
Связь «один ко многим» являются наиболее распространенным типом связи. В такого рода связях строка в таблице А может иметь много строк в таблице B. Но строка в таблице B может иметь только одну строку в таблице А. Например, таблицы «Издатели» и «Названия» имеют связь «один ко многим». То есть, каждый издатель выпускает много названий. Но каждое название принадлежит только одному издателю.
Связь «один ко многим» создается, если только один из связанных столбцов является основным ключом или имеет уникальное ограничение.
В окне связей в Access, сторона первичного ключа связи «один ко многим» обозначается номером 1. Сторона внешнего ключа связи обозначается символом бесконечности.
Связи «многие ко многим»
В связи «многие ко многим» строка в таблице А может иметь много совпадающих строк в таблице B, и наоборот. Вы создаете такую связь, определяя третью таблицу, которая называется промежуточной таблицей. Первичный ключ промежуточной таблицы состоит из внешних ключей как таблицы А, так и таблицы B. Например, таблица «Авторы» и таблица «Названия» имеют связь «многие ко многим», которая определяется связью «один ко многим» из каждой из этих таблиц к таблице «TitleAuthors». Первичным ключом таблицы «TitleAuthors» является комбинация столбца au_ID (первичный ключ таблицы «Authors») и столбца title_ID (первичный ключ таблицы «Titles»).
Связи «один к одному»
В связи «один к одному» строка в таблице А может иметь не более одной совпадающей строки в таблице B, и наоборот. Связь «один к одному» создается, если оба связанных столбца являются первичными ключами или имеют уникальные ограничения.
Этот тип отношений не распространен, потому что большая часть информации, которая связана таким образом, будет в одной таблице. Вы можете использовать связь «один к одному», чтобы предпринять следующие действия:
В Access сторона первичного ключа связи «один к одному» обозначается символом ключа. Сторона внешнего ключа также обозначается символом ключа.
Как определить связи между таблицами
При создании связи между таблицами связанные поля не должны иметь одни и те же имена. Однако связанные поля должны иметь один и тот же тип данных, если только поле первичного ключа не является полем AutoNumber. Вы можете сопоставить поле AutoNumber с полем Number, только если свойство FieldSize обоих совпадающих полей совпадает. Например, можно сопоставить поле AutoNumber и поле Number, если свойство theFieldSizeproperty обоих полей имеет значение Long Integer. Даже если оба совпадающих поля являются числовыми полями, они должны иметь параметр sameFieldSizeproperty.
Как определить связи «один ко многим» или «один к одному»
Чтобы создать связь «один ко многим» или «один к одному», выполните следующие действия.
Закройте все таблицы. Нельзя создавать или изменять связи между открытыми таблицами.
В Access 2002 и Access 2003 выполните следующие действия.
В Access 2007, Access 2010 или Access 2013 нажмите Связи в группе Показать/Скрыть на вкладке Инструменты базы данных.
Если вы еще не определили какие-либо связи в базе данных, автоматически отобразится диалоговое окно Показать таблицу. Если вы хотите добавить таблицы, которые нужно связать, но диалоговое окно Добавление таблицы не отображается, нажмите Добавить таблицу в меню Связи.
Дважды щелкните имена таблиц, которые необходимо связать, а затем закройте диалоговое окно Добавление таблицы. Чтобы создать связь между одной и той же таблицей, добавьте эту таблицу два раза.
Перетащите поле, которое вы хотите связать, из одной таблицы в связанное поле в другой таблице. Чтобы перетащить несколько полей, нажмите Ctrl, нажмите на каждое поле, а затем перетащите их.
В большинстве случаев вы перетаскиваете поле первичного ключа (это поле отображается жирным текстом) из одной таблицы в аналогичное поле (это поле часто имеет одно и то же имя), которое называется внешним ключом в другой таблице.
Откроется диалоговое окно Изменение связей. Убедитесь, что имена полей, отображаемые в двух столбцах, верны. Вы можете изменить имена, если это необходимо.
При необходимости установите параметры связей. Если вам нужна информация о конкретном элементе в диалоговом окне Изменение связей, нажмите кнопку со знаком вопроса, а затем щелкните элемент. (Эти параметры будут подробно описаны ниже в этой статье.)
Нажмите кнопку Создать, чтобы создать связь.
Повторите шаги с 4 по 7 для каждой пары таблиц, которые вы хотите связать.
При закрытии диалогового окна Изменение связей Access спрашивает, хотите ли вы сохранить макет. Сохраняете ли вы макет или не сохраняете макет, созданные вами связи сохраняются в базе данных.
Можно создавать связи не только в таблицах, но и в запросах. Однако целостность данных связывания не обеспечивается с помощью запросов.
Как определить связь «многие ко многим»
Чтобы создать связь «многие ко многим», выполните следующие действия.
Создайте две таблицы, которые будут иметь связь «многие ко многим».
Создайте третью таблицу. Это стыковочная таблица. В таблице соединения добавьте новые поля, которые имеют те же определения, что и основные ключевые поля из каждой таблицы, созданной в шаге 1. В связующей таблице основные ключевые поля функционируют как внешние ключи. Вы можете добавить другие поля в связующую таблицу, так же, как и в любую другую таблицу.
В связующей таблице установите первичный ключ, чтобы включить основные ключевые поля из двух других таблиц. Например, в связующей таблице «TitleAuthors» первичный ключ будет состоять из полей OrderID и ProductID.
Чтобы создать первичный ключ, выполните следующие действия:
Откройте таблицу в Конструкторе.
Выберите поле или поля, которые вы хотите определить в качестве первичного ключа. Чтобы выбрать одно поле, нажмите на селектор строки для нужного поля. Чтобы выбрать несколько полей, удерживайте клавишу Ctrl, а затем нажмите селектор строки для каждого поля.
В Access 2002 или в Access 2003 нажмите на Первичный ключ на панели инструментов.
В Access 2007 нажмите на Первичный ключ в группе Инструменты на вкладке Дизайн.
Если вы хотите, чтобы порядок полей в первичном ключе с несколькими полями отличался от порядка этих полей в таблице, нажмите Индексы на панели инструментов для отображения диалогового окна Indexes, а затем заново упорядочите имена полей для индекса с именем PrimaryKey.
Определите связь один-ко-многим между каждой основной и связующей таблицами.
Целостность данных
Референтная целостность — это система правил, которую использует Access, чтобы убедиться, что связи между записями в соответствующих таблицах действительны и что пользователь не удалит или не изменит случайно связанные с ними данные. Вы можете задать целостность данных при выполнении следующих условий:
При использовании целостности данных применяются следующие правила:
Каскадные обновления и удаления
Для соединений, в которых применяется целостность данных, можно указать, хотите ли вы, чтобы Access автоматически каскадно обновлял или каскадно удалял связанные с ним записи. Если вы установите эти параметры, удалите и обновите операции, которые обычно предотвращаются правилами целостности данных. При удалении записей или изменении основных ключевых значений в основной таблице Access вносит необходимые изменения в соответствующие таблицы для сохранения целостности данных.
Если установить флажок Каскадное обновление связанных полей при определении связей, то каждый раз при изменении первичного ключа записи в главной таблице Microsoft Access автоматически обновляет первичный ключ до нового значения во всех связанных записях. Например, при изменении идентификатора клиента в таблице «Клиенты», поле CustomerID в таблице «Заказы» автоматически обновляется для каждого из заказов этого клиента, чтобы связи не были нарушены. Access каскадирует обновления без отображения каких-либо сообщений.
Если первичным ключом в главной таблице является поле счетчика, выбор Каскадное обновление связанных полей не приводит ни к какому результату, поскольку невозможно изменить значение в поле счетчика.
Если выбрать Каскадное удаление связанных полей при определении связей, то при удалении записей в главной таблице Access автоматически удаляет связанные записи в соответствующей таблице. Например, при удалении записи клиента из таблицы «Клиенты», все заказы клиента автоматически удаляются из таблицы «Заказы». (Это включает записи в таблице «Детали заказа», которые связаны с записями «Заказы»). При удалении записей из формы или таблицы после установки флажка Каскадное удаление связанных записей Access выводит предупреждение, что связанные записи также могут быть удалены. Однако при удалении записей с помощью запроса удаления Access автоматически удаляет записи в соответствующих таблицах, не отображая предупреждение.
Типы соединения
Существует три основных типа соединения: Вы можете увидеть их на следующем снимке экрана:
Вариант 1 определяет внутреннее соединение. Внутреннее соединение — это соединение, в котором записи из двух таблиц объединяются в результатах запроса только в том случае, если значения в объединенных полях соответствуют определенному состоянию. В запросе соединение по умолчанию — это внутреннее соединение, которое выбирает записи только в том случае, если значения в объединенных полях совпадают.
Вариант 2 определяет левое внешнее соединение. Левое внешнее соединение — это соединение, в котором все записи с левой стороны операции LEFT JOIN в оператора запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на правой стороне.
Вариант 3 определяет правое внешнее соединение. Правое внешнее соединение — это соединение, в котором все записи с правой стороны операции RIGHT JOIN в операторе запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на левой стороне.