Удаление связанных записей в MySQL одним запросом
В MySQL есть возможность удалять связанные данные через внешние ключи (foreign key). Но это возможно только для некоторых движков БД (например, InnoDB) и в том случае, если для ключей задано ограничение ON DELETE CASCADE . В противном случае придется удалять записи вручную. Как раз об этом и пойдет речь в этой заметке.
Допустим у нас есть 2 таблицы: таблица товаров и изображений товаров:
- product (id, name, price, description, active)
- product_image (id, product_id, src, alt)
И нам необходимо удалить все неактивные товары вместе с изображениями. Для этого придется написать DELETE запрос с JOIN -ом и указать таблицы в которых необходимо произвести удаление данных.
Запрос, который решит указанную выше задачу, будет выглядеть следующим образом:
Если архитектура базы подразумевает связь еще через таблицу, то добавляем еще один джоин. Запрос удаляет только таблицы указанные после DELETE , остальные таблицы, которые участвуют в запросе, останутся без изменений.
MySQL Removing Some Foreign keys
I have a table whose primary key is used in several other tables and has several foreign keys to other tables.
The problem is that when I’m trying to drop one of the foreign key columns (ie locationIDX) it gives me an error.
«ERROR 1025 (HY000): Error on rename»
How can I drop the column in the assignment table above without getting this error?
11 Answers 11
As explained here, seems the foreign key constraint has to be dropped by constraint name and not the index name.
The foreign keys are there to ensure data integrity, so you can’t drop a column as long as it’s part of a foreign key. You need to drop the key first.
I would think the following query would do it:
As everyone said above, you can easily delete a FK. However, I just noticed that it can be necessary to drop the KEY itself at some point. If you have any error message to create another index like the last one, I mean with the same name, it would be useful dropping everything related to that index.
Check what’s the CONSTRAINT name and the FOREIGN KEY name:
Remove both the CONSTRAINT name and the FOREIGN KEY name:
Hope this helps!
Hey I followed some sequence above, and found some solution.
You will get FK Constrain Name like
Now you need to remove this constraints. by alter table commantd
Then drop the table column,
Here’s a way to drop foreign key constraint, it will work. ALTER TABLE location . location_id DROP FOREIGN KEY location_ibfk_1 ;
You usually get this error if your tables use the InnoDB engine. In that case you would have to drop the foreign key, and then do the alter table and drop the column.
But the tricky part is that you can’t drop the foreign key using the column name, but instead you would have to find the name used to index it. To find that, issue the following select:
SHOW CREATE TABLE region; This should show you a row ,at left upper corner click the +option ,the click the full text raio button then click the go .there you will get the name of the index, something like this:
CONSTRAINT region_ibfk_1 FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION Now simply issue an:
alter table region drop foreign key region_ibfk_1;
more simply just type:- alter table TableName drop foreign key TableName_ibfk_1;
remember the only thing is to add _ibfk_1 after your tablename to make like this:- TableName_ibfk_1
1 Введение
При фактической разработке таблица данных должна быть разработана в соответствии с содержанием объекта, и между объектами будут возникать различные ассоциации. Следовательно, существуют различные ассоциации между таблицами данных, основанные на дизайне сущностей.В MySQL существует три типа связей между таблицами данных.
2 Ассоциация
2.1 много-к-одному
Много-к-одному является наиболее распространенной ассоциацией в таблицах данных. Например, отношения между отделами и сотрудниками, отдел может иметь несколько сотрудников, и сотрудник не может принадлежать нескольким отделам, то есть строка в таблице отделов может иметь много совпадающих строк в таблице сотрудников, но строка в таблице сотрудников В таблице отделов может быть только одна подходящая строка.
Связь между таблицами устанавливается внешними ключами. В отношении таблицы «многие к одному» внешние ключи должны устанавливаться со многих сторон, в противном случае возникнет избыточность данных.
Примечание. Вы можете установить внешний ключ для нескольких сторон, чтобы сохранить первичный ключ другой стороны.
2.2 Многие ко многим
Многие-ко-многим также являются отношения в таблице данных. Например, отношения между студентами и курсами. Студент может выбрать несколько курсов. Конечно, курс также может быть выбран несколькими студентами. Много совпадающих строк, одна строка в учебной программе и много совпадающих строк в таблице ученика.
В общем, для достижения этой взаимосвязи вам нужно определить промежуточную таблицу, называемую таблицей соединений, которая будет иметь два внешних ключа, которые относятся соответственно к таблице учебного плана и таблице ученика. В отношении «многие ко многим» следует отметить, что два внешних ключа, которые присоединяются к таблице, являются повторяемыми, но отношение между двумя внешними ключами не может дублироваться, поэтому два внешних ключа являются объединением таблицы объединения. Первичный ключ.
В реальной жизни отношения между учениками и учителями также многим.
2.3 Один на один
Отношения один-к-одному более распространены в реальной жизни. Например, отношения между человеком и идентификационной картой — это отношения один-к-одному. Одному человеку соответствует одна идентификационная карта, и одна идентификационная карта может соответствовать только одному человеку. Как установить эту связь в таблице данных?
Прежде всего, чтобы отличить отношения «ведущий-ведомый», ведомой таблице необходимо, чтобы наличие главной таблицы было значимым, а удостоверение личности — значимым присутствием человека, поэтому человек является главной таблицей, а удостоверение личности — ведомой таблицей. Установить внешний ключ в таблице удостоверений личности. Ограниченный практическим опытом, внешний ключ в идентификационной карточке должен быть непустым и уникальным, поэтому первичный ключ в ведомой таблице (таблица идентификационной карточки) обычно используется непосредственно в качестве внешнего ключа.
Отношение «один к одному» может применяться к следующим аспектам.
1, разбить таблицу на несколько столбцов
2, изолировать часть таблицы по соображениям безопасности
3. Сохраните временные данные, и вы можете легко удалить эти данные, удалив таблицу.
2.4 Демонстрация дела
2.4.1 Материалы
Сначала добавьте ограничения внешнего ключа в таблицу ученик и класс таблицы, чтобы установить связь между двумя таблицами.
2.4.2 Создать ассоциацию
Приведенное выше имя таблицы фрагментов кода установило связь между таблицей ученика и таблицей оценок и успешно добавило ограничения внешнего ключа.
Из результатов запроса было успешно добавлено ограничение внешнего ключа для таблицы ученика. В настоящее время между учеником за столом и оценкой за столом существует отношение многих к одному.
2.4.3 Добавление данных
Поэтому в столбец внешнего ключа можно вставлять только те значения, которые существуют в ссылочном столбце, поэтому для добавления данных в две внешние таблицы сначала необходимо добавить данные для оценки первичной таблицы следующим образом:
Вы можете видеть, что в таблицу классов оценок были вставлены две данные, и добавленные идентификаторы первичного ключа равны 1 и 2. Поскольку внешний ключ таблицы учеников связан с первичным ключом таблицы оценок, при добавлении данных в таблицу учеников значение gid равно Может быть 1 или 2 и не может использовать другие значения.
Если в таблицу оценок не вставлено значение идентификатора, возникает следующая ошибка
Данные между двумя таблицами в приведенном выше фрагменте кода уже коррелированы. Например, чтобы узнать, какие учащиеся в классе программного обеспечения, вам нужно сначала найти идентификатор класса программного обеспечения, а затем запросить, какие учащиеся в классе, в соответствии с этим идентификатором. Конкретные шаги заключаются в следующем:
Затем запросите таблицу студентов для gid = 1 студентов, то есть студентов в классе программного обеспечения.
2.4.4 Удалить данные
В предыдущем разделе кратко объяснялось, как добавить данные в связанную таблицу, а в некоторых случаях вам необходимо удалить данные в связанной таблице. Например, как только класс школьного программного обеспечения отменен, класс и его ученики должны быть удалены вместе в базе данных. Поскольку существует связь между таблицей оценок и таблицей учеников, значения, на которые ссылается ссылочный столбец, удалить нельзя, поэтому при удалении класса 1 программного обеспечения сначала необходимо удалить всех учеников в классе, а затем удалить класс. Если вы удалите запись, которая все еще имеет отношение ссылки в таблице оценок, ошибка будет следующей:
m
Вышеуказанный процесс — это процесс правильного удаления таблицы ассоциации.
Примечание. При удалении данных в двух связанных таблицах необходимо сначала удалить данные в ведомой таблице, а затем удалить данные в основной таблице, в противном случае будет сообщено об ошибке.
Кроме того, в реальной ситуации учащиеся, которые хотят удалить «класс программного обеспечения» и не нуждаются в удалении «класса программного обеспечения», могут изменить значение «Li Qing» и «Wang Hong» в таблице учеников на NULL. Этот столбец в таблице может быть удален без ссылки. Однако, так как поле gid имеет ненулевое ограничение при создании таблицы, вы можете удалить только записи "Li Qing" и "Wang Hong".
3 Резюме
В этой статье подробно описывается определение отношения ассоциации при использовании MySQL, а также демонстрируется процесс добавления и удаления данных в таблице ассоциации.
Как удалить связь между таблицами в mysql
Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
Общий синтаксис установки внешнего ключа на уровне таблицы:
Для создания ограничения внешнего ключа после 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: