Как использовать внешние ключи с PHP
Итак, я понимаю, как создавать внешние ключи, и я знаю, какова цель FK. Но у меня есть проблема в понимании, как их использовать. Я задал вопрос относительно внешних ключей ЗДЕСЬ (нажмите на ссылку)
Вот что я сделал:
CREATE TABLE user(
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE items(
i_id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
price DECIMAL(8,2) NOT NULL,
PRIMARY KEY (i_id)
);
CREATE TABLE user_purchase(
i_id INT(11) NOT NULL,
name TINYTEXT NOT NULL,
id INT(11) NOT NULL,
FOREIGN KEY (i_id) REFERENCES items(i_id),
FOREIGN KEY (name) REFERENCES items(name),
FOREIGN KEY (id) REFERENCES user(id)
);
Теперь мой вопрос: как мне извлечь максимальную пользу из этого, используя PHP? По приведенной выше ссылке люди предположили, что в таблице user_purchase хорошо использовать только один внешний ключ, но что, если мне нужно несколько столбцов? Почему мы не используем несколько внешних ключей для разных столбцов одной таблицы?
Я использую mysql и php. Буду признателен, если вы покажете несколько примеров того, как вы используете PHP с таблицами, имеющими внешние ключи, для получения информации с помощью команд MYSQL. Мне действительно нужно подробное объяснение.
Мне также нужно понимать термины Нормализация и Денормализация. Я был бы признателен, если бы вы могли дать некоторые ссылки, которые подробно объясняют эти термины с помощью примеров, или если у вас есть какие-либо предложения для некоторых замечательных книг для начинающих по разработке, внедрению и т. Д. Баз данных, я был бы очень признателен.
Большое спасибо.
5 ответов
Столбцы / ограничения внешнего ключа
Итак, я понимаю, как создавать внешние ключи, и я знаю, какова цель FK. Но у меня есть проблема в понимании, как их использовать.
Предполагая, что вы ссылаетесь на ограничения внешнего ключа, краткий ответ будет: вы просто не используете их.
И вот идет длинный:
Мы привыкли называть столбцы внешними ключами других таблиц. Особенно во время процесса нормализации, фразы вроде user_purchase.i_id это внешний ключ к items таблица " будет очень распространенным явлением. Хотя это совершенно правильный способ описания отношений, он может стать немного размытым, когда мы достигнем фазы реализации.
Предположим, вы создали свои таблицы без FOREIGN KEY статьи:
CREATE TABLE user(
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE items(
i_id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
price DECIMAL(8,2) NOT NULL,
PRIMARY KEY (i_id)
);
CREATE TABLE user_purchase(
i_id INT(11) NOT NULL,
name TINYTEXT NOT NULL,
id INT(11) NOT NULL,
);
Обратите внимание, что в отношении столбцов внешнего ключа все еще реализованы. Там есть столбец, который ссылается на user Таблица (id) и еще один, который ссылается на items Таблица (i_id) - поставим name Колонка в стороне на мгновение. Рассмотрим следующие данные:
user user_purchase items
| id username | | id i_id | | i_id name price |
| 23 john | | 55 10 | | 10 chocolate bar 3.42 |
| 55 mary | | 70 10 | | 33 mobile phone 82.11 |
| 70 fred | | 70 33 | | 54 toothpaste 8.67 |
| 55 10 | | 26 toy car 6.00 |
| 70 26 |
Связь есть. Это осуществляется с помощью user_purchase таблица, в которой хранится информация о том, кто что купил. Если бы мы запросили в базе данных соответствующий отчет, мы бы сделали:
select * from user_purchase p
join user u on (p.id=u.id)
join items i on (p.i_id=i.i_id)
И вот как мы используем отношение и столбцы внешнего ключа.
Теперь, что если мы сделаем:
insert into user_purchase (id,i_id) values (23,99)
Видимо, это неверная запись. Хотя есть пользователь с id=23 нет элемента с i_id=99, СУБД позволит этому случиться, потому что она не знает ничего лучше. Еще.
Вот где ограничения внешнего ключа вступают в игру. Указав FOREIGN KEY (i_id) REFERENCES items(i_id) в user_purchase При определении таблицы мы, по сути, даем СУБД правило, которому нужно следовать: записи с i_id значения, которые не содержатся в items.i_id колонки не принимаются. Другими словами, в то время как столбец внешнего ключа реализует ссылку, ограничение внешнего ключа обеспечивает ссылочную целостность.
Обратите внимание, однако, что выше select не изменится, просто потому что вы определили ограничение FK. Таким образом, вы не используете ограничения FK, как в СУБД, чтобы защитить ваши данные.
Увольнение
... что если я хочу несколько столбцов? Почему мы не используем несколько внешних ключей для разных столбцов одной таблицы?
Спросите себя: зачем вам это? Если два внешних ключа будут служить одной и той же цели, избыточность в конечном итоге приведет к проблемам. Рассмотрим следующие данные:
user_purchase items
| id i_id name | | i_id name price |
| 55 10 chocolate bar | | 10 chocolate bar 3.42 |
| 70 10 chocolate bar | | 33 mobile phone 82.11 |
| 70 33 mobile phone | | 54 toothpaste 8.67 |
| 55 10 toothpaste | | 26 toy car 6.00 |
| 70 26 toy car |
Что не так с этой картиной? Сделал пользователь 55 купить две плитки шоколада, или плитку шоколада и зубную пасту? Такая двусмысленность может привести к большим усилиям по синхронизации данных, что было бы ненужным, если бы мы просто хранили один из внешних ключей. На самом деле, почему бы не бросить name столбец в целом, так как это подразумевается отношением.
Конечно, мы могли бы решить эту проблему путем реализации составного внешнего ключа, установив PRIMARY KEY(i_id,name) для items таблица (или определение дополнительного UNIQUE(i_id,name) индекс, это не имеет большого значения), а затем установив FOREIGN KEY(i_id,name) REFERENCES items(i_id,name), Таким образом, только (i_id,name) пары, которые существуют в items таблица будет действительна для user_purchases, Помимо того, что у вас все еще будет один внешний ключ, этот подход совершенно не нужен, при условии, что i_id столбца уже достаточно для идентификации элемента (не могу сказать то же самое для name колонка...).
Тем не менее, нет никаких правил против использования нескольких внешних ключей к таблице. На самом деле, есть обстоятельства, которые требуют такого подхода. Рассмотрим person(id,name) стол и parent(person,father,mother) один со следующими данными:
person parent
| id name | | person father mother |
| 14 John | | 21 14 59 |
| 43 Jane | | 14 76 43 |
| 21 Mike |
| 76 Frank |
| 59 Mary |
Очевидно, что все три столбца parent таблицы являются внешними ключами person, Не для одного и того же отношения, но для трех разных: поскольку родители человека тоже являются людьми, два соответствующих столбца должны ссылаться на одну и ту же таблицу person делает. Обратите внимание, однако, что три поля не только могут, но и должны ссылаться на разные person в том же parent ряд, так как никто не является его собственным родителем, а отец никого не является его матерью.
Внешние ключи используются в соединениях. Например, если вы хотите знать имена пользователей, которые приобрели определенный товар, вы должны написать:
select u.username
from items i
join user_purchase up on i.i_id = up.i_id
join user u on u.id = up.id
where i.name = "Some product name"
Они также могут использоваться самим ядром базы данных. Он может обнаружить, если вы создаете строку в user_purchase чья id или же i_id Столбец не соответствует ничему в указанном столбце в другой таблице.
Вы не должны копировать name столбец в user_purchase Таблица. name это просто атрибут item, это не характерно для какой-либо конкретной покупки. Если вам нужно получить название купленного предмета, присоединяйтесь к items Таблица.
Вместо того, чтобы читать так много ссылок, просто попробуйте реализовать это в любом простом проекте. Я просто объясняю, как мы будем использовать приведенные выше таблицы.
Предположим, вы 3 пользователей в user стол и 5 предметов в items Таблица.
user Таблица
id | username | password
1 abc 123
2 def 456
3 qwe 987
items Таблица
i_id | name | price
1 item 1 6
2 item 2 8
3 item 3 11
4 item 4 3
5 item 5 14
ваш user_purchase стол выглядит так
CREATE TABLE user_purchase( i_id INT(11) NOT NULL, id INT(11) NOT NULL, FOREIGN KEY (i_id) ССЫЛКИ элементы (i_id), FOREIGN KEY (id) REFERENCES user(id));
В этой таблице больше не нужно имя элемента. Итак, я удалил.
i_id | id
1 1
1 2
2 2
3 3
В приведенной выше таблице мы получим, пользователь 1 приобрел элемент 1, пользователь 2 купил элемент 1, элемент 2, а пользователь 3 приобрел элемент 3.
Вот как работает нормализация. Вы можете использовать MySQL JOIN для получения имени пользователя и деталей
SELECT B.user_name,C.name AS item_name,C.price
FROM user_purchase A
JOIN user B ON A.id = B.id
JOIN items C ON A.i_id = C.i_id
Вот внешний ключ, чтобы присоединиться
A.id = B.id
A.i_id = C.i_id
Просто глядя на точку нормализации / денормализации:
Нормализация - это процесс попытки удалить избыточность в вашей базе данных - в вашем примере name поле в user_purchase является избыточным - я могу узнать название элемента, посмотрев его в items использование таблицы i_id,
Так что, если бы мы смотрели на нормализацию user_purchase мы, вероятно, удалим name поле и использовать JOIN чтобы получить это, когда нам это нужно. Это, конечно, также означает, что нам не нужен второй FOREIGN KEY ссылка на items,
Денормализация в основном идет противоположным путем - добавление избыточности - обычно это делается из соображений производительности.
Тем не менее, в вашем примере вы также можете рассмотреть вопрос о нормализации по деловым причинам. Например, вы можете решить, что важно сохранить название продукта таким, каким оно было, когда пользователь фактически приобрел его (а не то, как оно называется сейчас) - на тот случай, если вам понадобится, например, перепечатать счет-фактуру. Однако даже в этом случае вы не захотите FOREIGN KEY вернуться к items (поскольку это "сломалось бы", если бы продукт был переименован).
Вы обрабатываете таблицы с внешними ключами в php так же, как если бы они не имели внешних ключей. Внешние ключи определены в базе данных и не имеют (почти) никакого отношения к php. Единственное, что вам нужно сделать в php, это реагировать на потенциальные ошибки, которые могут быть возвращены sql-запросами, которые нарушают ограничение внешнего ключа (обычно это DELETE-запросы).
А для вашей схемы базы данных вы должны удалить столбец "имя" из "таблицы user_purchase". Это избыточно.