Progress28.ru

IT Новости
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Phpmyadmin foreign keys

Phpmyadmin foreign keys

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

Общий синтаксис установки внешнего ключа на уровне таблицы:

Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE , которые задают действие при удалении и обновлении строки из главной таблицы соответственно.

Например, определим две таблицы и свяжем их посредством внешнего ключа:

В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:

CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.

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

RESTRICT : отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.

NO ACTION : то же самое, что и RESTRICT .

SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.

Каскадное удаление

Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE :

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

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

Setting up foreign keys in phpMyAdmin?

I’m setting up a database using phpMyAdmin. I have two tables ( foo and bar ), indexed on their primary keys. I am trying to create a relational table ( foo_bar ) between them, using their primary keys as foreign keys.

I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn’t support foreign keys. All id fields are INT(11) .

When I choose the foo_bar table, click the «relation view» link, and try to set the FK columns to be database.foo.id and database.bar.id , it says «No index defined!» beside each column.

What am I missing?

Clarification/Update

For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin.

Also, although I haven’t been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this:

It just makes me uncomfortable not to have the FKs explicitly defined in the database.

13 Answers 13

If you want to use phpMyAdmin to set up relations, you have to do 2 things. First of all, you have to define an index on the foreign key column in the referring table (so foo_bar.foo_id, in your case). Then, go to relation view (in the referring table) and select the referred column (so in your case foo.id) and the on update and on delete actions.

I think foreign keys are useful if you have multiple tables linked to one another, in particular, your delete scripts will become very short if you set the referencing options correctly.

Читать еще:  Php fopen кодировка

EDIT: Make sure both of the tables have the InnoDB engine selected.

phpMyAdmin lets you define foreign keys using their «relations» view. But since, MySQL only supports foreign constraints on «INNO DB» tables, the first step is to make sure the tables you are using are of that type.

To setup a foreign key so that the PID column in a table named CHILD references the ID column in a table named PARENT, you can do the following:

  1. For both tables, go to the operations tab and change their type to «INNO DB»
  2. Make sure ID is the primary key (or at least an indexed column) of the PARENT table.
  3. In the CHILD table, define an index for the PID column.
  4. While viewing the structure tab of the CHILD table, click the «relation view» link just above the «add fields» section.
  5. You will be given a table where each row corresponds to an indexed column in your CLIENT table. The first dropdown in each row lets you choose which TABLE->COLUMN the indexed column references. In the row for PID, choose PARENT->ID from the dropdown and click GO.

By doing an export on the CHILD table, you should see a foreign key constraint has been created for the PID column.

This is a summary of a Wikipedia article. It specifies the different types of relationships you can stipulate in PHPmyadmin. I am putting it here because it is relevant to @Nathan’s comment on setting the foreign keys options for «on update/delete» but is too large for a comment — hope it helps.

CASCADE

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).

RESTRICT

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

SET NULL

The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

SET DEFAULT

Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.

Denis.in.ua

Блог имени Меня

Использование внешних ключей в MySQL

Нет, внешние ключи (foreign keys) на самом деле не из Бразилии или Италии, и даже не из США. Для тех кто не в теме, они могут показаться странными. Но не бойтесь, мы здесь как раз для того, чтобы научить вас как с ними обращаться. Итак, что собой представляют внешние ключи?

Внешние ключи — это как раз то, что делает реляционные базы «реляционными» (от relation(англ.)- отношение, связь)(прим. переводчика: Как отметил в комментариях Дима Христов, на самом деле, базы названны реляционными из-за их группировки в таблицы, а не из-за связи между таблицами). Это как раз те связующие цепочки, которые связывают таблицы между собой. Они позволяют вам разместить «покупателей» в одной таблице, «заказы» в другой, а товары из этих заказов, в третьей, таким образом в базе минимизируется избыточность данных. Чем меньше избыточных данных — тем больше у вас шансов сохранить целостность данных (две или более противоречащие друг-другу записи — это всегда плохо).

Пример

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

CREATE TABLE usr (
usr_ >INT AUTO_INCREMENT NOT NULL ,
FIRST VARCHAR ( 25 ) NOT NULL ,
surname VARCHAR ( 50 ) NOT NULL ,
PRIMARY KEY ( usr_ >)
) ENGINE = InnoDB CHARACTER SET = UTF8;
CREATE TABLE product (
prod_ >INT AUTO_INCREMENT NOT NULL ,
name VARCHAR ( 40 ) NOT NULL ,
descr VARCHAR ( 255 ) NOT NULL ,
PRIMARY KEY ( prod_ >)
) ENGINE = InnoDB CHARACTER SET = UTF8;

Читать еще:  Php pdo install ubuntu

CREATE TABLE invoice (
inv_ >INT AUTO_INCREMENT NOT NULL ,
usr_ >INT NOT NULL ,
prod_ >INT NOT NULL ,
quantity INT NOT NULL ,
PRIMARY KEY ( inv_ >)
) ENGINE = InnoDB CHARACTER SET = UTF8;

А на это Диаграммы Отношений, для этих таблиц:

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

Могли бы мы хранить всю информацию в одной таблице? Конечно! Можно было бы создать таблицу, которая содержала бы в себе всю информацию о покупателе, данные о товаре и количество заказанных товаров. Но что случится, если мы допустим ошибку в одной из записей в описании товара или имени покупателя? О, нет, теперь все испорчено! Получим ли мы записи с опечатками вместе с другими записями? Что если нет? У нас некорректные данные — небеса рухнули.

Добавляем внешние ключи

Теперь пришло время установить связи между таблицами, которых не хватало до сих пор. Соединим колонки usr_id и prod_id таблицы invoice с их соответствиями в таблицах usr и product.

Вы можете выполнить команду ALTER для таблицы invoice, но я предпочитаю вносить такие изменения на этапе проектирования, поэтому можно внести изменения прямо запрос создания таблицы:

Заметьте, что в новом варианте запроса CREATE для таблицы invoice, я добавил синтаксис FOREIGN KEY ( ) REFERENCES table(
) чтобы установить связь между таблицами.

Просто добавив объявления внешних ключей, мы добились встроенной защиты целостности данных. Если мы попытаемся выполнить запрос INSERT или UPDATE со значением внешнего ключа для таблицы invoice, база данных автоматически проверит существует ли данное значение в связанной таблице. Если указанных значений в связанных таблицах не существует — база данных не выполнит запрос INSERT/UPDATE, сохранив таким образом целостность данных.

Теперь не придется проверять вручную родительскую таблицу на существование конкретных значений, прежде чем вставить данные в таблицу-потомок. Также можете спокойно удалять записи. Хотите избежать ошибок новым способом? Меньше кодирования — лучший способ для ленивых программистов.

Поддадим газку

Готовы улучшить ваши внешние ключи еще? Да, они могу сделать значительно больше для вас.

На данный момент, у нас есть защита целостности данных на случай каких-либо манипуляций с таблицами-потомками, но что если внести изменения в родительскую таблицу? Как нам быть уверенными, что таблицы-потомки в курсе всех изменений в родительской таблице?

MySQL позволяет нам контролировать таблицы-потомки во время обновления или удаления данных в родительской таблице с помощью подвыражений: ON UPDATE и ON DELETE. MySQL поддерживает 5 действий, которые можно использовать в выражениях ON UPDATE и/или ON DELETE.

  • CASCADE: если связанная запись родительской таблицы обновлена или удалена, и мы хотим чтобы соответствующие записи в таблицах-потомках также были обновлены или удалены. Что происходит с записью в родительской таблице, тоже самое произойдет с записью в дочерних таблицах. Однако не забывайте, что здесь можно легко попасться в ловушку бесконечного цикла.
  • SET NULL:если запись в родительской таблице обновлена или удалена, а мы хоти чтобы в дочерней таблице некоторым занчениям было присвоено NULL (конечно если поле таблицы это позволяет)
  • NO ACTION: смотри RESTRICT
  • RESTRICT:если связанные записи родительской таблицы обновляются или удаляются со значениями которые уже/еще содержатся в соответствующих записях дочерней таблицы, то база данных не позволит изменять записи в родительской таблице. Обе команды NO ACTION и RESTRICT эквивалентны отсутствию
    подвыражений ON UPDATE or ON DELETE для внешних ключей.
  • SET DEFAULT:На данный момент эта команда распознается парсером, но движок InnoDB никак на нее не реагирует.

Для моей базы данных из примера, я решил, что для внешних ключей из таблицы invoice, UPDATE будут выполняться каскадно для дочерних таблиц, а удаление будет запрещено. Таким образом, любые изменения в таблицах usr и product автоматически отразятся в таблице invoice, но если товар заказан или если у пользователя есть счет — они не могут быть удалены.

Ниже представлен новый вариант запроса CREATE для таблицы invoice с внешними ключами и выражениями ON UPDATE и ON DELETE

Выводы

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

Читать еще:  Manager index php

Настройка внешних ключей в phpMyAdmin?

Я создаю базу данных с помощью phpMyAdmin. У меня есть две таблицы ( foo и bar ), индексируются по их основным ключам. Я пытаюсь создать реляционную таблицу ( foo_bar ) между ними, используя их первичные ключи как внешние ключи.

Я создал эти таблицы как MyISAM, но с тех пор изменил все три на InnoDB, потому что я читал, что MyISAM не поддерживает внешние ключи. Все поля id INT(11) .

Когда я выбираю таблицу foo_bar , щелкните ссылку «отношение» и постарайтесь установить столбцы FK как database.foo.id и database.bar.id , в ней указано «Без индекса!» рядом с каждой колонкой.

Что мне не хватает?

Разъяснение /Update

Для простоты я хочу продолжать использовать phpMyAdmin. В настоящее время я использую XAMPP, что достаточно просто, чтобы я мог сосредоточиться на PHP/CSS/Javascript, и он поставляется с phpMyAdmin.

Кроме того, хотя мне еще не удалось установить явные внешние ключи, у меня есть реляционная таблица и вы можете выполнять такие объединения:

Мне просто неудобно не иметь явно определенных FK в базе данных.

Если вы хотите использовать phpMyAdmin для установления отношений, вам нужно сделать 2 вещи. Прежде всего, вам нужно определить индекс в столбце внешнего ключа в ссылочной таблице (так что foo_bar.foo_id, в вашем случае). Затем перейдите к представлению отношения (в ссылочной таблице) и выберите соответствующий столбец (так что в вашем случае foo.id) и действия по обновлению и удалению.

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

РЕДАКТИРОВАТЬ: Убедитесь, что обе таблицы имеют выбранный двигатель InnoDB.

phpMyAdmin позволяет вам определять внешние ключи, используя их «отношения». Но поскольку MySQL поддерживает только внешние ограничения в таблицах «INNO DB», первый шаг состоит в том, чтобы убедиться, что используемые вами таблицы имеют такой тип.

Чтобы настроить внешний ключ, чтобы столбец PID в таблице с именем CHILD ссылался на столбец идентификатора в таблице с именем PARENT, вы можете сделать следующее:

  • Для обеих таблиц перейдите на вкладку операций и измените их тип на «INNO DB»
  • Убедитесь, что идентификатор — это первичный ключ (или, по крайней мере, индексированный столбец) таблицы PARENT.
  • В таблице CHILD определите индекс для столбца PID.
  • При просмотре закладки структуры таблицы CHILD нажмите ссылку «отношение» непосредственно над разделом «добавить поля».
  • Вам будет предоставлена ​​таблица, в которой каждая строка соответствует индексированному столбцу в вашей таблице CLIENT. Первый раскрывающийся список в каждой строке позволяет вам выбрать, какой TABLE- > COLUMN ссылается на индексированный столбец. В строке для PID в раскрывающемся списке выберите PARENT- > ID и нажмите GO.

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

Это краткое изложение статьи в Википедии. Он определяет различные типы отношений, которые вы можете установить в PHPmyadmin. Я помещаю это здесь, потому что это относится к комментарию @Nathan по установке параметров внешних ключей для «при обновлении/удалении», но слишком велик для комментария — надеюсь, это поможет.

CASCADE

Всякий раз, когда строки в основной (ссылающейся) таблице удаляются (соответственно обновляются), соответствующие строки дочерней (ссылающейся) таблицы с соответствующим столбцом внешнего ключа также удаляются (соответственно обновляются). Это называется каскадным удалением (соответственно update [2]).

RESTRICT

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

БЕЗДЕЙСТВИЕ

НЕТ ДЕЙСТВИЙ и ОГРАНИЧЕНИЯ очень похожи. Основное различие между NO ACTION и RESTRICT заключается в том, что при NO ACTION проверка ссылочной целостности выполняется после попытки изменить таблицу. RESTRICT выполняет проверку перед попыткой выполнения оператора UPDATE или DELETE. Оба ссылочных действия действуют одинаково в случае сбоя проверки ссылочной целостности: оператор UPDATE или DELETE приведет к ошибке.

SET NULL

Значения внешнего ключа в ссылочной строке устанавливаются в NULL, когда ссылочная строка обновляется или удаляется. Это возможно только в том случае, если соответствующие столбцы в ссылочной таблице обнуляются. Из-за семантики NULL для ссылочной строки с NULL в столбцах внешнего ключа не требуется ссылочная строка.

УСТАНОВИТЬ ПО УМОЛЧАНИЮ

Как и в случае SET NULL, значения внешнего ключа в ссылочной строке устанавливаются в столбец по умолчанию при обновлении или удалении ссылочной строки.

Ссылка на основную публикацию
Adblock
detector