Как сделать связь 1 к 1 sql
Базы данных могут содержать таблицы, которые связаны между собой различными связями. Связь (relationship) представляет ассоциацию между сущностями разных типов.
При выделении связи выделяют главную или родительскую таблицу (primary key table / master table) и зависимую, дочернюю таблицу (foreign key table / child table). Дочерняя таблица зависит от родительской.
Для организации связи используются внешние ключи. Внешний ключ представляет один или несколько столбцов из одной таблицы, который одновременно является потенциальным ключом из другой таблицы. Внешний ключ необязательно должен соответствовать первичному ключу из главной таблицы. Хотя, как правило, внешний ключ из зависимой таблицы указывает на первичный ключ из главной таблицы.
Связи между таблицами бывают следующих типов:
Один к одному (One to one)
Один к многим (One to many)
Многие ко многим (Many to many)
Связь один к одному
Данный тип связей встречает не часто. В этом случае объекту одной сущности можно сопоставить только один объект другой сущности. Например, на некоторых сайтах пользователь может иметь только один блог. То есть возникает отношение один пользователь — один блог.
Нередко этот тип связей предполагает разбиение одной большой таблицы на несколько маленьких. Основная родительская таблица в этом случае продолжает содержать часто используемые данные, а дочерняя зависимая таблица обычно хранит данные, которые используются реже.
В этом отношении первичный ключ зависимой таблицы в то же время является внешним ключом, который ссылается на первичный ключ из главной таблицы.
Например, таблица Users представляет пользователей и имеет следующие столбцы:
UserId (идентификатор, первичный ключ)
Name (имя пользователя)
И таблица Blogs представляет блоги пользователей и имеет следующие столбцы:
BlogId (идентификатор, первичный и внешний ключ)
Name (название блога)
В этом случае столбец BlogId будет хранить значение из столбца UserId из таблицы пользователей. То есть столбец BlogId будет выступать одновременно первичным и внешним ключом.
Связь один ко многим
Это наиболее часто встречаемый тип связей. В этом типе связей несколько строк из дочерний таблицы зависят от одной строки в родительской таблице. Например, в одном блоге может быть несколько статей. В этом случае таблица блогов является родительской, а таблица статей — дочерней. То есть один блог — много статей. Или другой пример, в футбольной команде может играть несколько футболистов. И в то же время один футболист одновременно может играть только в одной команде. То есть одна команда — много футболистов.
К примеру, пусть будет таблица Articles, которая представляет статьи блога и которая имеет следующие столбцы:
ArticleId (идентификатор, первичный ключ)
BlogId (внешний ключ)
Title (название статьи)
Text (текст статьи)
В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.
Связь многие ко многим
При этом типе связей одна строка из таблицы А может быть связана с множеством строк из таблицы В. В свою очередь одна строка из таблицы В может быть связана с множеством строк из таблицы А. Типичный пример — студенты и курсы: один студент может посещать несколько курсов, и соответственно на один курс могут записаться несколько студентов.
Другой пример — статьи и теги: для одной статьи можно определить несколько тегов, а один тег может быть определен для нескольких статей.
Но в SQL Server на уровне базы данных мы не можем установить прямую связь многие ко многим между двумя таблицами. Это делается посредством вспомогательной промежуточной таблицы. Иногда данные из этой промежуточной таблицы представляют отдельную сущность.
Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца:
TagId (идентификатор, первичный ключ)
Text (текст тега)
Также пусть будет промежуточная таблица ArticleTags со следующими полями:
TagId (идентификатор, первичный и внешний ключ)
ArticleIdId (идентификатор, первичный и внешний ключ)
Технически мы получим две связи один-ко-многим. Столбец TagId из таблицы ArticleTags будет ссылаться на столбец TagId из таблицы Tags. А столбец ArticleId из таблицы ArticleTags будет ссылаться на столбец ArticleId из таблицы Articles. То есть столбцы TagId и ArticleId в таблице ArticleTags представляют составной первичный ключ и одновременно являются внешними ключами для связи с таблицами Articles и Tags.
Ссылочная целостность данных
При изменении первичных и внешних ключей следует соблюдать такой аспект как ссылочная целостность данных (referential integrity). Ее основная идея состоит в том, чтобы две таблице в базе данных, которые хранят одни и те же данные, поддерживали их согласованность. Целостность данных представляет правильно выстроенные отношения между таблицами с корректной установкой ссылок между ними. В каких случаях целостность данных может нарушаться:
Аномалия удаления (deletion anomaly). Возникает при удалении строки из главной таблицы. В этом случае внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы
Аномалия вставки (insertion anomaly). Возникает при вставке строки в зависимую таблицу. В этом случае внешний ключ из зависимой таблицы не соответствует первичному ключу ни одной из строк из главной таблицы.
Аномалии обновления (update anomaly). При подобной аномалии несколько строк одной таблицы могут содержать данные, которые принадлежат одному и тому же объекту. При изменении данных в одной строке они могу прийти в противоречие с данными из другой строки.
Аномалия удаления
Для решения аномалии удаления для внешнего ключа следует устанавливать одно из двух ограничений:
Если строка из зависимой таблицы обязательно требует наличия строки из главной таблицы, то для внешнего ключа устанавливается каскадное удаление. То есть при удалении строки из главной таблицы происходит удаление связанной строки (строк) из зависимой таблицы.
Если строка из зависимой таблицы допускает отсутствие связи со строкой из главной таблицы (то есть такая связь необязательна), то для внешнего ключа при удалении связанной строки из главной таблицы задается установка значения NULL. При этом столбец внешнего ключа должен допускать значение NULL.
Аномалия вставки
Для решения аномалии вставки при добавлении в зависимую таблицу данных столбец, который представляет внешний ключ, должен допускать значение NULL. И таким образом, если добавляемый объект не имеет связи с главной таблицей, то в столбце внешнего ключа будет стоять значение NULL.
Аномалии обновления
Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.
One-to-One, One-to-Many Table Relationships in SQL Server
Consider the following example. Book table (pk_book_id, title, ISBN) is associated with Author (pk_author_id, author_name, phone_no, fk_book_id). One book can have many authors. This relationship can be implemented by using (PK_Author_Id as Primary Key) and (Fk_Author_Id as Foreign Key).
Types of Relationships
a. One-One Relationship (1-1 Relationship)
b. One-Many Relationship (1-M Relationship)
c. Many-Many Relationship (M-M Relationship)
This tech-recipe covers only 1-1 and 1-M relationship.
1. One-One Relationship (1-1 Relationship)
One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-Unique foreign key constraints.
With One-to-One Relationship in SQL Server, for example, a person can have only one passport. Let’s implement this in SQL Server.
One-to-One Relationship is implemented using dbo.Person(Pk_Person_Id) as the Primary key and dbo.PassportDetails(fk_person_id) as (Unique Key Constraint-Foreign Key).
Therefore, it will always have only one matching row between the Person-PassportDetails table based on the dbo.Person(Pk_Person_Id)-dbo.PassportDetails(Fk_Person_Id) relationship.
1. Create two Tables (Table A & Table B) with the Primary Key on Both the tables.
2. Create Foreign key in Table B which references the Primary key of Table A.
3. Add a Unique Constraint on the Foreign Key column of Table B.
What happens if we try to insert passport details for the same fk_person_id which already exists in the passportDetails table?
We get an error of Unique key violation.
2. One-Many Relationship (1-M Relationship)
The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship.
In the One-to-Many Relationship in SQL Server, for example, a book can have multiple authors. Let’s implement this in SQL Server.
One-to-Many Relationship is implemented using dbo.Book(Pk_Book_Id) as the Primary Key and dbo.Author (Fk_Book_Id) as (Foreign Key). Thus, it will always have only One-to-Many (One Book-Multiple Authors) matching rows between the Book-Author table based on the dbo.Book (Pk_Book_Id)-dbo.Author(Fk_Book_Id) relationship.
1. Create two Tables (Table A & Table B) with the Primary Key on both the tables.
2. Create a Foreign key in Table B which references the Primary key of Table A.
jtest.ru
Сегодня мы продолжаем наше путешествие в мире SQL и реляционных систем управления базами данных. В этой части мы научимся работать с несколькими таблицами связанными между собой. Сначала мы познакомимся с базовыми концепциями, а потом начнем работать с запросами JOIN в SQL.
Предыдущие статьи
Вступление
При проектировании базы данных, здравый смысл подсказывает нам, что мы должны использовать различные таблицы для разных данных. Пример: клиенты, заказы, записи, сообщения и т.д. Так же мы должны иметь взаимосвязи между этими таблицами. Например, клиент имеет заказы, а у заказа есть позиции (товары). Эти взаимосвязи должны быть отражены в базе данных. А также, когда мы получаем данные с помощью SQL, мы должны использовать определенные типы запросов JOIN, чтобы получить нужный результат.
Вот несколько типов отношений в базе данных. Сегодня мы рассмотрим следующие:
- Отношения один к одному
- Один ко многим и многие к одному
- Многие ко многим
- Связь с самим собой
Когда данные выбираются из нескольких связанных таблиц, мы будем использовать запрос JOIN. Есть несколько типов присоединения, мы познакомимся с этими:
- Cross Joins (Перекрестное соединение)
- Natural Joins (Естественное соединений)
- Inner Joins (Внутреннее соединений)
- Left (Outer) Joins (Левое (внешнее) соединение)
- Right (Outer) Joins (Правое (внешнее) соединение)
Также мы изучим предложения ON и USING.
Связь один к одному
Допустим есть таблица покупателей (customers):
Мы можем расположить информацию о адресе покупателя в другой таблице:
Теперь у нас есть связь между таблицами покупателей (Customers) и адресами (Addresses). Если каждый адрес может принадлежать только одному покупателю, то такая связь называется «Один к одному». Имейте ввиду, что такой тип отношений не очень распространен. Наша первоначальная таблица, в которой информация о покупателе и его адресе хранилась вместе, в большинстве случаев работает нормально.
Обратите внимание, что теперь поле с названием «address_id», в таблице покупателей, ссылается на соответствующую запись в таблице адресов. Оно называется внешним ключом (Foreign Key) и используется во всех видах связей в базе. Мы рассмотрим этот вопрос позже в этой статье.
Вот так можно отобразить отношения между покупателями и адресами:
Обратите внимание, что существование данных отношений не обязательно, например, может существовать запись о покупателе без связанной записи о его адресе.
Связь один ко многим и многие к одному
Этот тип отношений наиболее часто встречающийся. Рассмотрим такой сайт интернет магазина:
- У покупателей может быть несколько заказов.
- Заказ может содержать несколько товаров.
- Товары могут иметь описание на нескольких языках.
В этих случаях нам потребуется создать связь «Один ко многим». Пример:
Каждый покупатель может иметь 0 или более заказов. Но каждый заказ может принадлежать только одному покупателю.
Связь многие ко многим
В некоторых случаях требуется многочисленные связи по обе стороны отношений. Например, каждый заказ может содержать множество товаров. И каждый товар может присутствовать во многих заказах.
Для такой связи нам потребуется создать дополнительную таблицу:
Назначение таблицы «Items_Orders» только одно — создать связь «Многие ко многим» между товарами и заказами.
Так можно представить этот тип отношений:
Если добавить записи items_orders к диаграмме, то она будет выглядеть так:
Связь с собой
Такой тип используется когда у таблицы должны быть связь с собой. Допустим у Вас есть реферальная программа. Покупатели могут ссылаться на других покупателей на вашем сайте интернет магазина. Таблица может выглядеть так:
Покупатели 102 и 103 ссылаются на покупателя 101.
Этот тип похож на связь «Один ко многим», поскольку один покупатель может ссылаться на несколько покупателей. Это можно представить как древовидную структуру:
Один покупатель может ссылаться на одного покупателя, на нескольких покупателей, или вообще не ссылаться ни на одного.
Если Вы хотите создать связь внутри таблицы «многие ко многим», то потребуется создать дополнительную таблицу, такую же как и в предыдущей части.
Внешние ключи
Пока что мы говорили только о базовых вещах. Пришло время применить полученные знания на практике, используя SQL. В данной часть нам нужно понять, что из себя представляют внешние ключи.
В отношениях, обсуждаемых выше, у нас всегда было поле вида «****_id», которое ссылалось столбец в другой таблице. В нашем примере столбец customer_id, в таблице Orders, является внешним ключом:
В таких базах как MySQL есть два способа создания внешних ключей:
Задать внешний ключ явно
Создадим простую таблицу с покупателями:
Теперь создадим таблицу заказов, которая будет содержать вторичный ключ:
Оба столбца (customers.customer_id и orders.customer_id) должны быть одного типа. Если у первого тип INT, то второй не должен быть типа BIGINT, например.
Пожалуйста, помните, что в MySQL полностью поддерживает внешние ключи только подсистема InnoDB. Другие подсистемы хранения данных позволяют определять внешние ключи без каких либо ошибок. Столбцы с внешними ключами индексируются автоматически, если явно не задать другой индекс.
Без явного объявления
Некоторые таблицы заказов могут быть созданы без явного определения внешнего ключа:
Когда данные получают запросом JOIN, Вы можете использовать столбец как внешний ключ, хотя база данных не знает о этих связях.
Мы подошли к изучению запросов JOIN, которые обсудим далее в статье.
Отображение связей
В данный момент, моей любимой программой для проектирования баз данных и отображения связей является MySQL Workbench.
После того как Вы спроектировали базу данных, ее можно экспортировать в SQL и выполнить на сервере. Это очень удобно при создании больших и сложных баз данных.
Запросы JOIN
Чтобы получить связанные данные из базы данных следует использовать запросы JOIN.
Прежде чем мы начнем, давайте создадим для работы тестовые таблицы и данные.
У нас есть 4 покупателя. У одного из них два заказа, у двоих по одному заказу, и у одного вообще нет заказов. Теперь давайте посмотрим какие виды запросов JOIN мы можем выполнять с этими таблицами.
Cross Join (Перекрестное объединение)
Это вид JOIN запроса по-умолчанию, если не определено условие.
Результатом будет, так называемое, «Декартово объединение» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Т.к. в каждой таблице по 4 строки, мы получили в результате 16 строк.
Ключевое слово JOIN можно заменить на запятую, в этом случае.
Конечно такой результат почти бесполезен. Давайте взглянем на другие виды объединений.
Natural Join (Естественное объединение)
При таком виде запроса JOIN таблицы должны иметь совпадающие, по имени, столбцы. В нашем случае в обеих таблицах должен присутствовать столбец customer_id. MySQL объединит записи только в случае совпадения значений в этих столбцах.
Как Вы можете видеть, в этот раз столбец customer_id отображаются только один раз, потому что движок базы рассматривает этот столбец как общий. Мы видим два заказа Adam’а, и другие два заказа Joe и Sandy. Наконец мы получили некоторую полезную информацию.
Inner Join (Внутреннее объединение)
Если условие объединения не указан, то выполняется внутреннее объединение. В этом случае хорошей идеей будет наличие совпадений по полю customer_id в обеих таблицах. Результат должен быть аналогичен естественному объединению.
Результат почти такой же. Столбец customer_id повторяется два раза, по разу для каждой таблицы. Объясняется это тем, что мы попросили базу сравнить значение по двум столбцам. При этом не знаю, что возвращают одну и туже информацию.
Добавим побольше условий к запросу.
На этот раз возвращается только те заказы, сумма которых превышает $15.
Предложение ON
Прежде чем перейти к другим видам объединяющих запросов, нам нужно рассмотреть предложение ON. Оно служит для вставки условий JOIN в отдельные предложения.
Теперь мы можем различать условия, относящиеся к JOIN и условия в части WHERE. Но еще есть небольшая разница в функционировании. Мы увидим это, когда перейдем к примерам с LEFT JOIN.
Предложение USING
Предложение USING немного похоже на конструкцию ON. Если столбцы в таблицах называется одинаково, можно указать их здесь.
На самом деле это очень похоже на NATURAL JOIN, т.е. объединяющий столбец (customer_id) не повторяется дважды.
Left (Outer) Join (Левое внешнее соединение)
LEFT JOIN это вид внешнего соединения. В следующем запросе, если не найдены совпадения во второй таблице, записи из первой таблице все равно отобразятся.
Хотя у Andy и нет заказов, эта запись все равно отображается. Значение из второй таблицы равно NULL.
Это полезно, когда нужно найти записи, у которых нет связей. Например, мы можем найти всех покупателей, которые ничего не заказывали.
Все что мы сделали — нашли все значения NULL для order_id.
Отметим, что ключевое слово OUTER не обязательно. Вы можете использовать просто LEFT JOIN вместо LEFT OUTER JOIN.
Условия
Теперь давайте посмотрим на запросы с условиями.
Так, что случилось с Andy и Sandy? LEFT JOIN подразумевает, что мы должны получить покупателей, у которых нет заказов. Проблема в том, что условие WHERE скрывает эти результаты. Чтобы получить их, мы можем попытаться включить условие с NULL.
Появился Andy, но нет Sandy. Выглядит неправильно. Для того чтобы получить то, что мы хотим, нужно использовать предложение ON.
Теперь мы получили всех, и все заказы более $15. Как я говорил ранее, предложение ON иногда работает не так как WHERE. В таких внешних объединениях как это, столбцы включаются всегда, даже если нет совпадений в условии предложения ON.
Right (Outer) Join (Правое внешнее соединение)
Объединение RIGHT OUTER JOIN работает также, только порядок таблиц меняется на обратный.
На этот раз мы не получили результатов с NULL, потому что каждый заказ имеет сопоставление с записью покупателя. Мы можем поменять порядок таблиц и получим тот же результат, что и с LEFT OUTER JOIN.
Теперь у нас появились значения NULL, потому что таблица покупателей с правой стороны от объединения.
How to Create a real one-to-one relationship in SQL Server
I have two tables Country and Capital , I set Capital ‘s primary key as foreign key which references Country ‘s primary. But when I use Entity Framework database-first, the model is 1 to 0..1.
How does one create a one-to-one relationship in SQL Server?
8 Answers 8
I’m pretty sure it is technically impossible in SQL Server to have a True 1 to 1 relationship, as that would mean you would have to insert both records at the same time (otherwise you’d get a constraint error on insert), in both tables, with both tables having a foreign key relationship to each other.
That being said, your database design described with a foreign key is a 1 to 0..1 relationship. There is no constraint possible that would require a record in tableB. You can have a pseudo-relationship with a trigger that creates the record in tableB.
So there are a few pseudo-solutions
First, store all the data in a single table. Then you’ll have no issues in EF.
Or Secondly, your entity must be smart enough to not allow an insert unless it has an associated record.
UPDATE
To explain in REALITY how 1 to 1 relationships don’t work, I’ll use the analogy of the Chicken or the egg dilemma. I don’t intend to solve this dilemma, but if you were to have a constraint that says in order to add a an Egg to the Egg table, the relationship of the Chicken must exist, and the chicken must exist in the table, then you couldn’t add an Egg to the Egg table. The opposite is also true. You cannot add a Chicken to the Chicken table without both the relationship to the Egg and the Egg existing in the Egg table. Thus no records can be every made, in a database without breaking one of the rules/constraints.
Database nomenclature of a one-to-one relationship is misleading. All relationships I’ve seen (there-fore my experience) would be more descriptive as one-to-(zero or one) relationships.
UPDATE EF 5.0 — one-to-one Support
While SQL Server will still allow the dependent row to be null. Entity Framework Core 5.0 now allows you to configure dependent properties as required. EF 5 What’s new
In EF Core 5.0, a navigation to an owned entity can be configured as a required dependent. For example:
Set the foreign key as a primary key, and then set the relationship on both primary key fields. That’s it! You should see a key sign on both ends of the relationship line. This represents a one to one.
This can be done by creating a simple primary foreign key relationship and setting the foreign key column to unique in the following manner:
Check to see if everything is fine
Now Generally in Primary Foreign Relationship (One to many), you could enter multiple times EmployeeID , but here an error will be thrown
The above statement will show error as
Violation of UNIQUE KEY constraint ‘UQ__Salary__7AD04FF0C044141D’. Cannot insert duplicate key in object ‘dbo.Salary’. The duplicate key value is (1).
There is one way I know how to achieve a strictly* one-to-one relationship without using triggers, computed columns, additional tables, or other ‘exotic’ tricks (only foreign keys and unique constraints), with one small caveat.
I will borrow the chicken-and-the-egg concept from the accepted answer to help me explain the caveat.
It is a fact that either a chicken or an egg must come first (in current DBs anyway). Luckily this solution does not get political and does not prescribe which has to come first — it leaves it up to the implementer.
The caveat is that the table which allows a record to ‘come first’ technically can have a record created without the corresponding record in the other table; however, in this solution, only one such record is allowed. When only one record is created (only chicken or egg), no more records can be added to any of the two tables until either the ‘lonely’ record is deleted or a matching record is created in the other table.
Add foreign keys to each table, referencing the other, add unique constraints to each foreign key, and make one foreign key nullable, the other not nullable and also a primary key. For this to work, the unique constrain on the nullable column must only allow one null (this is the case in SQL Server, not sure about other databases).
To insert, first an egg must be inserted (with null for Chicken). Now, only a chicken can be inserted and it must reference the ‘unclaimed’ egg. Finally, the added egg can be updated and it must reference the ‘unclaimed’ chicken. At no point can two chickens be made to reference the same egg or vice-versa.
To delete, the same logic can be followed: update egg’s Chicken to null, delete the newly ‘unclaimed’ chicken, delete the egg.
This solution also allows swapping easily. Interestingly, swapping might be the strongest argument for using such a solution, because it has a potential practical use. Normally, in most cases, a one-to-one relationship of two tables is better implemented by simply refactoring the two tables into one; however, in a potential scenario, the two tables may represent truly distinct entities, which require a strict one-to-one relationship, but need to frequently swap ‘partners’ or be re-arranged in general, while still maintaining the one-to-one relationship after re-arrangement. If the more common solution were used, all data columns of one of the entities would have to be updated/overwritten for all pairs being re-arranged, as opposed to this solution, where only one column of foreign keys need to be re-arranged (the nullable foreign key column).
Well, this is the best I could do using standard constraints (don’t judge 🙂 Maybe someone will find it useful.
