Mysql связь «многие ко многим» — пример sql кода таблиц с пояснениями. таблица связи (on delete cascade). получение данных

Поведение отношений

Можно настроить поведение отношения 1:N для поддержки бизнес-правил организации. Почему это может потребоваться? Рассмотрим пример.

Допустим, у вас новый продавец и требуется назначить ему несколько существующих возможных сделок, в данное время назначенных другому продавцу. Каждая запись возможной сделки может иметь несколько действий задач, связанных с ней. Можно легко найти активные возможные сделки, которые требуется переназначить, и назначить их новому продавцу. Но что произойдет с действиями задач, связанными с возможными сделками? Хотелось бы вам открывать каждую задачу и указывать, должна ли она также быть назначена новому продавцу? Скорее всего, нет. Вместо этого можно разрешить отношению применить некоторые стандартные правила автоматически. Эти правила применяются только к записям задач, связанным с возможными сделками, которые вы переназначаете. Это отношение сущностей называется Opportunity_Tasks. Можно выполнить следующие действия:

• Переназначить все активные задачи.

• Переназначить все задачи. Это поведение принимается по умолчанию.

• Не переназначать задачи.

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

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

Каждое из этих действий можно настроить для управления тем, как действия будут распространяться на записи, связанные с записью основной сущности отношением сущностей 1:N. Параметры поведения представлены в следующей таблице.

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

Значения ON DELETE и ON UPDATE

CASCADE — Каскадное удаление и редактирование. Эта настройка означает, что при удалении каталога, все товары из него тоже удалятся. При редактировании, если мы изменим id каталога, у товаров автоматически изменится поле «catalog_id».

RESTRICT — При этой настройке, если мы попытаемся удалить каталог, в котором есть товары, или изменить его id, база данных выдаст нам ошибку и удаление не состоится.

SET NULL — Из названия видно, что если исчезнет(удалится или изменится) каталог с таким id, то у товаров в поле «catalog_id» установится значение NULL

С этой настройкой нужно вести себя осторожно, потому что по умолчанию индексы «NOT NULL»

NO ACTION — Игнорируем удаление и редактирование каталога, и пусть в поле «catalog_id» будет несуществующий идентификатор, просто игнорируем это.

Связь один-к-одному

Последнее обновление: 31.10.2015

Строго говоря в Entity Framework нет как таковой связи один-к-одному, так как ожидается, что обработчик будет использовать связь
один-ко-многим. Но все же нередко возникает потребность в наличие подобной связи между объектами в приложении, и в Entity Framework мы можем настроить
данный тип отношений.

Рассмотрим стандартный пример подобных отношений: есть класс пользователя User, который хранит логин и пароль, то есть данные учетных записей. А все данные профиля, такие
как имя, возраст и так далее, выделяются в класс профиля UserProfile.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
//.................................

public class User
{
    public int Id { get; set; }
    public string Login { get; set; }
    public string Password { get; set; }

    public UserProfile Profile { get; set; }
}

public class UserProfile
{
    
    
    public int Id { get; set; }

    public string Name { get; set; }
    public int Age { get; set; }

    public User User { get; set; }
}

В этой связи между классами класс UserProfile является дочерним или подчиненным по отношению к классу User. И чтобы установить связь одни к одному,
у подчиненного класса устанавливается свойство идентификатора, которое называется также, как и идентификатор в основном классе. То есть в классе User свойство называется
Id, то и в UserProfile также свойство называется Id. Если бы в классе User свойство называлось бы UserId, то такое же название должно было быть и в UserProfile.

И в классе UserProfile над этим свойством Id устанавливаются два атрибута: , который показывает, то это первичный ключ, и
, который показывает, что это также и внешний ключ. Причем внешний ключ к таблице объектов User.

Соответственно классы User и UserProfile имеют ссылки друг на друга.

В классе контекста определяются свойства для взаимодействия с талицами в бд:

public class UserContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<UserProfile> UserProfiles { get; set; }
}

Для этих классов контекст данных будет создавать следующую таблицу UserProfiles:

CREATE TABLE . (
       INT            NOT NULL,
     NVARCHAR (MAX) NULL,
      INT            NOT NULL,
    CONSTRAINT  PRIMARY KEY CLUSTERED ( ASC),
    CONSTRAINT  FOREIGN KEY () REFERENCES . ()
);

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

using(UserContext db = new UserContext())
{
    User user1 = new User { Login = "login1", Password = "pass1234" };
    User user2 = new User { Login = "login2", Password = "5678word2" };
    db.Users.AddRange(new List<User> { user1, user2 });
    db.SaveChanges();
    UserProfile profile1 = new UserProfile { Id = user1.Id, Age = 22, Name = "Tom" };
    UserProfile profile2 = new UserProfile { Id = user2.Id, Age = 27, Name = "Alice" };
    db.UserProfiles.AddRange(new List<UserProfile> { profile1, profile2 });
    db.SaveChanges();

    foreach(User user in db.Users.Include("Profile").ToList())
        Console.WriteLine("Name: {0}  Age: {1}  Login: {2}  Password: {3}", 
                user.Profile.Name, user.Profile.Age, user.Login, user.Password);
}

Редактирование:

using (UserContext db = new UserContext())
{
    User user1 = db.Users.FirstOrDefault();
    if(user1!=null)
    {
        user1.Password = "dsfvbggg";
        db.Entry(user1).State = EntityState.Modified;
        db.SaveChanges();
    }

    UserProfile profile2 = db.UserProfiles.FirstOrDefault(p => p.User.Login == "login2");
    if(profile2!=null)
    {
        profile2.Name = "Alice II";
        db.Entry(profile2).State = EntityState.Modified;
        db.SaveChanges();
    }
}

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

using (UserContext db = new UserContext())
{
    User user1 = db.Users.Include("Profile").FirstOrDefault();
    if(user1!=null)
    {
        db.UserProfiles.Remove(user1.Profile);
        db.Users.Remove(user1);
        db.SaveChanges();
    }

    UserProfile profile2 = db.UserProfiles.FirstOrDefault(p => p.User.Login == "login2");
    if(profile2!=null)
    {
        db.UserProfiles.Remove(profile2);
        db.SaveChanges();
    }
}

НазадВперед

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

При создании связи между таблицами связанные поля не должны иметь одни и те же имена. Однако связанные поля должны иметь один и тот же тип данных, если только поле первичного ключа не является полем AutoNumber. Вы можете сопоставить поле AutoNumber с полем Number, только если свойство FieldSize обоих совпадающих полей совпадает. Например, можно сопоставить поле AutoNumber и поле Number, если свойство theFieldSizeproperty обоих полей имеет значение Long Integer. Даже если оба совпадающих поля являются числовыми полями, они должны иметь параметр sameFieldSizeproperty.

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

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

1. Закройте все таблицы. Нельзя создавать или изменять связи между открытыми таблицами.

2. В Access 2002 и Access 2003 выполните следующие действия.

   1. Нажмите F11, чтобы переключиться в окно базы данных.
   2. В меню Инструменты выберите Связи.

   В Access 2007, Access 2010 или Access 2013 нажмите Связи в группе Показать/Скрыть на вкладке Инструменты базы данных.

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

4. Дважды щелкните имена таблиц, которые необходимо связать, а затем закройте диалоговое окно Добавление таблицы. Чтобы создать связь между одной и той же таблицей, добавьте эту таблицу два раза.

5. Перетащите поле, которое вы хотите связать, из одной таблицы в связанное поле в другой таблице. Чтобы перетащить несколько полей, нажмите Ctrl, нажмите на каждое поле, а затем перетащите их.

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

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

   При необходимости установите параметры связей. Если вам нужна информация о конкретном элементе в диалоговом окне Изменение связей, нажмите кнопку со знаком вопроса, а затем щелкните элемент. (Эти параметры будут подробно описаны ниже в этой статье.)

7. Нажмите кнопку Создать, чтобы создать связь.

8. Повторите шаги с 4 по 7 для каждой пары таблиц, которые вы хотите связать.

   При закрытии диалогового окна Изменение связей Access спрашивает, хотите ли вы сохранить макет. Сохраняете ли вы макет или не сохраняете макет, созданные вами связи сохраняются в базе данных.

   Примечание

   Можно создавать связи не только в таблицах, но и в запросах. Однако целостность данных связывания не обеспечивается с помощью запросов.

Как определить связь «многие ко многим»

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

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

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

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

   Примечание

   Чтобы создать первичный ключ, выполните следующие действия:

   1. Откройте таблицу в Конструкторе.

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

   3. В Access 2002 или в Access 2003 нажмите на Первичный ключ на панели инструментов.

      В Access 2007 нажмите на Первичный ключ в группе Инструменты на вкладке Дизайн.

      Примечание

      Если вы хотите, чтобы порядок полей в первичном ключе с несколькими полями отличался от порядка этих полей в таблице, нажмите Индексы на панели инструментов для отображения диалогового окна Indexes, а затем заново упорядочите имена полей для индекса с именем PrimaryKey.

4. Определите связь один-ко-многим между каждой основной и связующей таблицами.

Добавление необходимых таблиц к представлению источника данных

Откройте конструктор представлений источника данных для представления источников данных Adventure Works DW 2012 .

Щелкните правой кнопкой мыши панель Организатор диаграмм , выберите команду Создать диаграмму и укажите Причина заказа через Интернет в качестве имени созданной диаграммы.

Перетащите таблицу InternetSales с панели Таблицы на панель Диаграмма .

Щелкните правой кнопкой мыши панель Диаграмма и выберите команду Добавить или удалить таблицы.

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

В меню Формат выберите команду Автоматический макет и щелкните значок Диаграмма.

В окне свойств измените свойство FriendlyName таблицы DimSalesReason на SalesReason, затем измените свойство FriendlyName таблицы FactInternetSalesReason на InternetSalesReason.

На панели Таблицы разверните узел InternetSalesReason (dbo.FactInternetSalesReason), щелкните столбец SalesOrderNumber и просмотрите в окне свойств свойство DataType для этого столбца данных.
Обратите внимание, что в качестве типа данных для столбца SalesOrderNumber указан тип данных string.

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

На панели Таблицы щелкните правой кнопкой мыши таблицу InternetSalesReason (dbo.FactInternetSalesReason) и выберите пункт Просмотр данных.
Обратите внимание, что для каждого номера строки каждого заказа значение ключа указывает причину покупки данной позиции линии, как показано на следующем рисунке.

Целостность данных

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

• Совпадающие поля из основной таблицы являются первичным ключом или имеет уникальный индекс.
• Связанные поля имеют один и тот же тип данных. Из этого правила есть два исключения: Поле счетчика может быть связано с полем номера, которое имеет значение свойства «Длинное целое», а поле счетчика, имеющее значение свойства «Код репликации», может быть связано с полем номера, которое имеет значение свойства «Код репликации».
• Обе таблицы относятся к одной и той же базе данных Access. Если таблицы являются связанными таблицами, они должны быть таблицами в формате Access, и необходимо открыть базу данных, в которой они хранятся, чтобы установить целостность данных. Референтная целостность не может быть применена для связанных таблиц из баз данных в других форматах.

При использовании целостности данных применяются следующие правила:

• Невозможно ввести значение во внешнем ключевом поле связанной таблицы, которое не существует в первичном ключе первичной таблицы. Тем не менее, можно ввести значение Null во внешнем ключе. Это указывает на то, что записи не связаны между собой. Например, невозможно иметь заказ, который назначается клиенту, который не существует. Тем не менее, можно иметь заказ, который не назначается никому, введя значение Null в поле CustomerID.
• Вы не можете удалить запись из основной таблицы, если в соответствующей таблице существуют соответствующие записи. Например, вы не можете удалить запись сотрудника из таблицы «Сотрудники», если в таблице «Заказы» есть заказы, назначенные сотруднику.
• Невозможно изменить основное ключевое значение в основной таблице, если эта запись имеет соответствующие записи. Например, вы не можете изменить идентификатор сотрудника в таблице «Сотрудники», если в таблице «Заказы» есть заказы, назначенные этому сотруднику.

Алиасы

Согласитесь, в прошлом примере пришлось довольно много букв написать. Чтобы этого избежать, в запросах можно использовать алиасы для имён таблиц. Для этого после имени таблицы можно написать AS alias. Давайте для таблицы users зададим алиас — u, а для таблицы profiles — p. Эти алиасы теперь можно использовать в любой части запроса:

Заметьте, запрос сократился. Писать запрос с использованием алиаса быстрее.

Как уже говорилось выше, алиас можно использовать в любой части запроса, в том числе и в условии WHERE:

Один-ко-многим

При такой связи одной записи в одной таблице соответствует несколько записей в другой. В начале этого урока мы рассмотрели как раз такой пример, когда говорили о добавлении в таблицу с новостями поля author_id. Таким образом, у каждой статьи есть один автор. В то же время у одного автора может быть несколько статей.
Давайте создадим таблицу для статей. Пусть в ней будет идентификатор статьи, её название, текст, и идентификатор автора.

Добавим несколько статей:

Запросим теперь эти записи, чтобы убедиться, что всё ок

Давайте теперь выведем имена статей вместе с авторами. Для этого снова воспользуемся оператором INNER JOIN.

Как видим, у Ивана две статьи, и ещё одна у Ольги.

Если бы мы захотели на странице со статьей выводить рядом с автором краткую информацию о нем, нам нужно было бы сделать ещё один JOIN на табличку profiles.

Изи!

INNER JOIN

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

Чтобы получить всех пользователей вместе с их профилями нам нужно выполнить следующий запрос:

Каждая строка из левой таблицы, сопоставляется с каждой строкой из правой таблицы, после этого проверяется условие.

Если мы хотим выбрать только некоторые столбцы, то после оператора SELECT нужно перед именем поля явно указать название таблицы, из которой оно берется:

Создание и изменение отношений 1:N между сущностями

Откройте обозреватель решений.

В разделе Компоненты раскройте узел Сущности, затем раскройте сущность, с которой требуется работать.

Выберите Отношения 1:N.

Чтобы изменить отношение или просмотреть сведения для отношения, выберите отношение и нажмите на панели инструментов «Действия» кнопку Другие действия, затем выберите Изменить.
— ИЛИ —
Чтобы добавить новое отношение, выберите Создать отношение «один ко многим».

Важно!
Если кнопка Создать отношение «один ко многим» не отображается на панели инструментов «Действия», то создать отношение 1:N для этой сущности невозможно.

Для нового отношения в разделе Определение отношения выберите в списке Связанная сущность сущность для связывания.

Примечание
При указании связанной сущности задается значение по умолчанию в поле Имя. Если изменить связанную сущность перед ее сохранением, соответственно изменится и значение поля Имя.

Выберите, будет ли это поле доступно для поиска или нет.

В разделе Поле поиска укажите значение для поля в поле Отображаемое имя.

Важно!
При указании значения Отображаемое имя задается значение по умолчанию в поле Имя

Если изменить Отображаемое имя поля поиска перед сохранением данных, значение в поле Имя не изменится. Поэтому необходимо ввести в поле Имя информативное значение перед сохранением данных.

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

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

В разделе Поведение отношений выберите в списке Тип отношений один из следующих вариантов.

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

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

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

Настраиваемое каскадное. В настраиваемом каскадном отношении между двумя сущностями выбирается поведение, связанное с каждым из наборов возможных действий.

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

Дополнительные сведения:

1. Выберите Сохранить и закрыть, чтобы закрыть форму Отношение.

2. Выполнив настройки, опубликуйте их:

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

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

Примечание

• Настраиваемая сущность не может быть основной в каскадном отношении со связанной системной сущностью. Это означает, что между основной настраиваемой сущностью и связанной системной сущностью не может быть отношений с каким-либо из действий, установленным в «Передавать всем», «Передавать активным» или «Передавать владельцу».
• У новых отношений действие не может иметь значение Передавать всем, Передавать активным или Передавать владельцу, если связанная сущность в этом отношении уже является связанной сущностью в любом другом отношении, действие которого имеет значение Передавать всем, Передавать активным или Передавать владельцу. Это позволяет избежать создания отношений с несколькими родительскими сущностями.
• После каждого изменения элементов пользовательского интерфейса или внедрения скриптов формы для сущности необходима публикация изменений. Все изменения в схеме данных приложения, таких как настраиваемые сущности, связи или поля, применяются сразу.
• Если отношение является частью управляемого решения, разработчик решения может ограничить настройку отношения пользователями.
• Установка решения или публикация настроек может помешать нормальной работе системы. Рекомендуется запланировать импорт решения в оптимальный для пользователей период.

Общие нюансы для связей

1. В контексте процесса доступно только два варианта связей 1-1 и 1-N (особенность контекста процесса, потому что доступно редактирование контекста без перезагрузки ELMA, но это уже другая история)
2. Будьте аккуратны с копированием свойств — списков:
   • 1-N — при копировании проверяйте ключевую колонку
     • если запустить «как есть» будет общая связь для разных свойств — что в последствии нарушит целостность базы данных (будут битые связи) и при перестроении базы данных — система не запустится — бомба замедленного действия (починить можно — как расскажу в следующий раз)
     • если открыть свойство на редактирование — ключевая колонка будет пустой и потребуется ее заполнить — тогда все будет хорошо
   • N-N — при копировании проверяйте название развязочной таблицы
     • если запустить «как есть» будет общая развязочная таблица для разных свойств — будут путаться списки и в последствии нарушится целостность базы данных и при перестроении базы данных ELMA не запустится — снова бомба замедленного действия
     • если открыть свойство на редактирование — система не даст сохранить развязочную таблицу с наименованием, которое уже есть в системе — тогда все будет хорошо
   • N-N (инверсия) — при копировании проверяйте ключевую колонку, как с 1-N — последствия те же
3. Не приравнивайте в коде один список к другому
   • нельзя писать Клиент1.Заказы = Клиент2.Заказы, при сохранении будет ошибка — найдены общие связи коллекций
   • нужно писать Клиент1.Заказы.AddAll( Клиент2.Заказы)
4. В объектах ELMA BPM разработчики решили даже в незаполненных объектах создавать пустые коллекции
5. Для создания списков — старайтесь не использовать блоки, тем более если элемент списка — независимый объект.(иногда для создания списка новички создают блок с одним свойством (получается развязочная таблица) — знайте это костыль

Бонус

Списки можно вывести таблицей — выведите на форму «Список связанных сущностей»

• со связями 1-N и N-N(инверсия) можно указать ключевую колонку и получится удобная таблица:
• со связью N-N придется заморочиться и придумать EQL вот из статья базы знаний