Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удаление из связанных таблиц / 2 сообщений из 2, страница 1 из 1
21.08.2015, 20:36:41
    #39034239
AndrewInc
AndrewInc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление из связанных таблиц
Уважаемые коллеги, прошу помощи в удалении из связанных таблиц.
Есть три таблицы: товаров, картинок и таблица связки между ними. Все таблицы типа InnoDB.
Когда пользователь пытается удалить товар необходимо удалить и все с ним связанные картинки а так-же связи товар-картинка.
Естественно можно это выполнить тремя запросами... но это некрасиво. Хочется сделать один DELETE чтобы он удалит связи, затем товар и картинки ему принадлежащие.
Экспериментировал с DELETE весь день... упарился, задачу не решил.
Вернее, можно сделать такой вот запрос:
Код: sql
1.
2.
3.
4.
5.
DELETE pi, p, i
FROM product_image pi 
  LEFT JOIN product p ON (pi.prod_id=p.prod_id)
  LEFT JOIN image i ON (i.img_id=pi.img_id) 
WHERE (pi.prod_id=1);


и он удалит из для первого товара как саму карточку, так и картинки, а так-же связи между ними. Вот только если у товара нет картинок - тогда он ничего не удалит, а хотелось бы карточку товара всё-же удалять. Казалось бы в условии WHERE вместо pi.prod_id следует указать p.prod_id и тогда выборка будет относительно таблицы товаров - но нет, MySQL этого не допускает. Пишет:
Код: plaintext
1.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint
fails (`ozon`.`product_image`, CONSTRAINT `fk_product_has_image_product1` FOREIGN KEY (`prod_id`) REFERENCES `product` (`prod_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)	0.000 sec
Если перестроить запрос вокруг таблицы товаров:
Код: sql
1.
2.
3.
4.
5.
DELETE pi, i, p
FROM product p
  LEFT JOIN product_image pi ON (pi.prod_id=p.prod_id)
  LEFT JOIN image i ON (i.img_id=pi.img_id)
WHERE (p.prod_id=1);


то MySQL пишет ровно то же самое:
Код: plaintext
1.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint
fails (`ozon`.`product_image`, CONSTRAINT `fk_product_has_image_product1` FOREIGN KEY (`prod_id`) REFERENCES `product` (`prod_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)	0.000 sec
Пробовал тот же порядок таблиц как и в первом запросе с WHERE из второго но с RIGTH join'ом:
Код: sql
1.
2.
3.
4.
5.
DELETE pi, p, i
FROM product_image pi
  RIGHT JOIN product p ON (pi.prod_id=p.prod_id)
  LEFT JOIN image i ON (i.img_id=pi.img_id)
WHERE (p.prod_id=1);


результат не оригинален:
Код: plaintext
1.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint
fails (`ozon`.`product_image`, CONSTRAINT `fk_product_has_image_product1` FOREIGN KEY (`prod_id`) REFERENCES `product` (`prod_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)	0.000 sec

В общем, тут я загрустил... и начал уже подумывать - ну её эту крастоту, может таки - хряпнуть на два DELETE
Код: sql
1.
2.
DELETE pi, i FROM product_image pi LEFT JOIN image i ON (i.img_id=pi.img_id)  WHERE (pi.prod_id=1);
DELETE FROM product WHERE prod_id=1;


Но предполагаю, что "прожёные базисты" меня сейчас заклюют.

Если у кого есть предположения как выйти из тупика - буду рад.
Пробовал
Код: plaintext
DELETE IGNORE ..
и
Код: plaintext
DELETE LOW_PRIORITY ..

но например вариация третьего запроса с IGNORE - работает, но странно - удаляет картинки и связки, а карточку товара оставляет.

В общем - идеи закончились.

Если кому будет интересно, побороться с задачей, вот подробности:
Код: plaintext
Server version:         5.5.25 MySQL Community Server (GPL)
Схема данных во вложении к посту, а исходный код:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
CREATE TABLE image (
  img_id int(11) NOT NULL AUTO_INCREMENT,
  src varchar(255) DEFAULT NULL,
  mime varchar(100) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  PRIMARY KEY (img_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO image (img_id, src, mime, `type`) VALUES(4, 'file.xls', 'application/vnd.ms-excel', 'application');
INSERT INTO image (img_id, src, mime, `type`) VALUES(5, '#2817_moneyList.jpg', 'image/jpeg', 'image');
INSERT INTO image (img_id, src, mime, `type`) VALUES(6, '#2816_confirm.png', 'image/png', 'image');
INSERT INTO image (img_id, src, mime, `type`) VALUES(7, '#2816_confirm1.png', 'image/png', 'image');
INSERT INTO image (img_id, src, mime, `type`) VALUES(8, '#2816_confirm2.png', 'image/png', 'image');
INSERT INTO image (img_id, src, mime, `type`) VALUES(9, 'TU2_1.GIF', 'image/gif', 'image');
INSERT INTO image (img_id, src, mime, `type`) VALUES(10, 'Фрагмент1.frw', 'application/octet-stream', 'application');
INSERT INTO image (img_id, src, mime, `type`) VALUES(11, '#2816_confirm2.1.jpg', 'image/jpeg', 'image');

CREATE TABLE product (
  prod_id int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  content longtext,
  PRIMARY KEY (prod_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO product (prod_id, `name`, content) VALUES(1, 'календари', 'Бла-бла');
INSERT INTO product (prod_id, `name`, content) VALUES(2, 'визитки', 'бла-бла2');
INSERT INTO product (prod_id, `name`, content) VALUES(3, 'визитки', 'бла-бла2');
INSERT INTO product (prod_id, `name`, content) VALUES(4, 'визитки', 'бла-бла2');
INSERT INTO product (prod_id, `name`, content) VALUES(5, 'визитки', 'бла-бла2');
INSERT INTO product (prod_id, `name`, content) VALUES(6, 'визитки', 'бла-бла2');
INSERT INTO product (prod_id, `name`, content) VALUES(9, 'trans-test3', NULL);

CREATE TABLE product_image (
  prod_id int(11) NOT NULL,
  img_id int(11) NOT NULL,
  sort float DEFAULT NULL,
  PRIMARY KEY (prod_id,img_id),
  KEY fk_product_has_image_image1_idx (img_id),
  KEY fk_product_has_image_product1_idx (prod_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 4, 0);
INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 5, 0);
INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 6, 0);
INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 7, 0);
INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 8, 0);
INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 9, 0);
INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 10, 0);
INSERT INTO product_image (prod_id, img_id, sort) VALUES(1, 11, 0);

ALTER TABLE `product_image`
  ADD CONSTRAINT fk_product_has_image_product1 FOREIGN KEY (prod_id) REFERENCES product (prod_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2015, 23:09:27
    #39034283
AndrewInc
AndrewInc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление из связанных таблиц
Попробовал с другими таблицами. Вдруг я с CONSTRAINT'ами накосячил... создал таблицу c (категория), таблицу t (товар) и связал их таблицей c_t

в товарах есть строчка t_id=7 вне категорий, для t_id=6 напротив - две категории (в остальных по одной) В общем запрос:
Код: sql
1.
select c.*, c_t.*, t.* FROM t LEFT JOIN c_t ON (t.t_id=c_t.t_id) LEFT JOIN c ON (c_t.c_id=c.c_id);


приводит к ответу:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
c_id, cname, c_id, t_id, t_id, tname
'1', 'name1', '1', '1', '1', 'name1'
'2', 'name2', '2', '2', '2', 'name2'
'3', 'name3', '3', '3', '3', 'name3'
'4', 'name4', '4', '4', '4', 'name4'
'5', 'name5', '5', '5', '5', 'name5'
'6', 'name6', '6', '6', '6', 'name6'
'7', 'name7', '7', '6', '6', 'name6'
NULL, NULL, NULL, NULL, '7', '7-- name'

Стоит выполнить удаление товара без категорий:
Код: sql
1.
2.
3.
4.
start transaction;
delete  c_t, c, t from t LEFT JOIN c_t ON (c_t.t_id=t.t_id) LEFT JOIN c ON (c.c_id=c_t.c_id) where t.t_id=7;
select c.*, c_t.*, t.* FROM t LEFT JOIN c_t ON (t.t_id=c_t.t_id) LEFT JOIN c ON (c_t.c_id=c.c_id);
rollback;



Последняя строка удаляется:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
c_id, cname, c_id, t_id, t_id, tname
'1', 'name1', '1', '1', '1', 'name1'
'2', 'name2', '2', '2', '2', 'name2'
'3', 'name3', '3', '3', '3', 'name3'
'4', 'name4', '4', '4', '4', 'name4'
'5', 'name5', '5', '5', '5', 'name5'
'6', 'name6', '6', '6', '6', 'name6'
'7', 'name7', '7', '6', '6', 'name6'

а вот тот же DELETE ... where t.t_id=6 возвращает ошибку
автор23:05:58 delete c_t, c, t from t LEFT JOIN c_t ON (c_t.t_id=t.t_id) LEFT JOIN c ON (c.c_id=c_t.c_id) where t.t_id=6 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`ozon`.`c_t`, CONSTRAINT `fk_c_has_t_t1` FOREIGN KEY (`t_id`) REFERENCES `t` (`t_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) 0.000 sec

Код этого примера:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
CREATE TABLE c (
  c_id int(11) NOT NULL AUTO_INCREMENT,
  cname varchar(45) DEFAULT NULL,
  PRIMARY KEY (c_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO c (c_id, cname) VALUES(1, 'name1');
INSERT INTO c (c_id, cname) VALUES(2, 'name2');
INSERT INTO c (c_id, cname) VALUES(3, 'name3');
INSERT INTO c (c_id, cname) VALUES(4, 'name4');
INSERT INTO c (c_id, cname) VALUES(5, 'name5');
INSERT INTO c (c_id, cname) VALUES(6, 'name6');
INSERT INTO c (c_id, cname) VALUES(7, 'name7');

CREATE TABLE c_t (
  c_id int(11) NOT NULL,
  t_id int(11) NOT NULL,
  PRIMARY KEY (c_id,t_id),
  KEY fk_t_idx (t_id),
  KEY fk_c_idx (c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO c_t (c_id, t_id) VALUES(1, 1);
INSERT INTO c_t (c_id, t_id) VALUES(2, 2);
INSERT INTO c_t (c_id, t_id) VALUES(3, 3);
INSERT INTO c_t (c_id, t_id) VALUES(4, 4);
INSERT INTO c_t (c_id, t_id) VALUES(5, 5);
INSERT INTO c_t (c_id, t_id) VALUES(6, 6);
INSERT INTO c_t (c_id, t_id) VALUES(7, 6);

CREATE TABLE t (
  t_id int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(45) DEFAULT NULL,
  PRIMARY KEY (t_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO t (t_id, tname) VALUES(1, 'name1');
INSERT INTO t (t_id, tname) VALUES(2, 'name2');
INSERT INTO t (t_id, tname) VALUES(3, 'name3');
INSERT INTO t (t_id, tname) VALUES(4, 'name4');
INSERT INTO t (t_id, tname) VALUES(5, 'name5');
INSERT INTO t (t_id, tname) VALUES(6, 'name6');
INSERT INTO t (t_id, tname) VALUES(7, '7-- name');


ALTER TABLE `c_t`
  ADD CONSTRAINT fk_c FOREIGN KEY (c_id) REFERENCES `c` (c_id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT fk_c_has_t_t1 FOREIGN KEY (t_id) REFERENCES `t` (t_id) ON DELETE NO ACTION ON UPDATE NO ACTION;



схема, для визуального представление во вложении
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удаление из связанных таблиц / 2 сообщений из 2, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 310ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1832793]:
 0ms
get settings:
 8ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 37ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 20ms
get tp. blocked users:
 1ms
others: 222ms
total:  310ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]