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

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

Листинг 2.16. Код создания трех таблиц со связью многие ко многим

-- Создаем таблицу фамилий
CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 vcLastName varchar(50),
 vcSurName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

-- Создаем таблицу телефонов
CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)
)

-- Создаем связующую таблицу
CREATE TABLE LinkTable
(
 idLinkTable uniqueidentifier DEFAULT NEWID(),
 idName uniqueidentifier, 
 idPhone uniqueidentifier, 

 CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable),

 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Phones (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

Это немного модифицированный пример того, что мы рассматривали в разделе 1.2.6. В качестве ключевого поля используется уникальный идентификатор GUID.

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

Листинг 2.17. Добавление записи в три связанных таблицы

-- Вставляем записи в связанные таблицы
DECLARE @ID1 uniqueidentifier
DECLARE @ID2 uniqueidentifier

-- Генерируем идентификаторы
SET @ID1=NEWID()
SET @ID2=NEWID()

-- Вставить жителя
INSERT INTO Names 
VALUES(@ID1, 'ИВАНОВ', 'ИВАН', 'ИВАНОВИЧ')

-- Вставить в таблицу телефонов
INSERT INTO Phones 
VALUES(@ID2, '39927321')

-- Вставить строку в связующую таблицу
INSERT INTO LinkTable (idName, idPhone) 
VALUES(@ID1, @ID2)

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

Теперь давайте посмотрим, как работает эта связь и отобразим фамилию и соответствующий номер телефона:

SELECT vcName, vcPhone 
FROM Names n, Phones p, LinkTable l
WHERE n.idName=l.idName AND p.idPhone=l.idPhone

В результате вы должны увидеть таблицу из двух полей:

vcName                   vcPhone    
------------------------ ---------- 
ИВАНОВ                   39927321

(1 row(s) affected)

Жизнь упрощает то, что в качестве первичного ключа используется уникальный идентификатор, который достаточно просто сгенерировать на стороне клиента или до выполнения вставки данных и использовать в операторе INSERT. Если в качестве первичного ключа используется число с автоматическим увеличением, то, вставив одну строку с номером телефона, мы не знаем, какой ключ был ей назначен, и не можем навести связь. Необходимо сначала определить этот ключ запросом SELECT. Но эта проблема решаема. Пусть решение не очень элегантно, но оно есть:

Посмотрим на код из листинга 2.18. Здесь мы наполняем таблицы и устанавливаем связь без предварительной генерации уникального идентификатора для первичного ключа.

Листинг 2.18. Наполнение таблицы значениями

-- Заполняем фамилиями
INSERT INTO Names (vcName) VALUES('ИВАНОВ')
INSERT INTO Names (vcName) VALUES('ПЕТРОВ')
INSERT INTO Names (vcName) VALUES('СИДОРОВ')

-- Заполняем номерами телефонов
INSERT INTO Phones (vcPhone) VALUES('12387264')
INSERT INTO Phones (vcPhone) VALUES('45435345')
INSERT INTO Phones (vcPhone) VALUES('89349234')

-- Наводим связь 1
DECLARE @ID1 uniqueidentifier
DECLARE @ID2 uniqueidentifier

SELECT @ID1=idName FROM Names WHERE vcName='ИВАНОВ'
SELECT @ID2=idPhone FROM Phones WHERE vcPhone='12387264'

INSERT INTO LinkTable (idName, idPhone) 
VALUES(@ID1, @ID2)
...
...

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