Обновление по внешнему ключу

Темы, не касающиеся фреймворка, но относящиеся к программированию в целом.
Ответить
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Обновление по внешнему ключу

Сообщение slo_nik »

День добрый.
Есть две таблицы, "товары(mtrl_goods)" и "общие данные(mtrl_joint)".
Структура таблиц:

Код: Выделить всё

CREATE TABLE IF NOT EXISTS `mtrl_goods`( 
                                         `id_goods` INT(3) NOT NULL AUTO_INCREMENT, 
                                         # разные поля 
                                         `sh`       INT(2) DEFAULT NULL, 
                                         # разные поля 
                                         PRIMARY KEY(`id_goods`), 
                                         KEY `sh`(`sh`) 
                                       )ENGINE=InnoDB DEFAULT CHARSET="utf8"; 

CREATE TABLE IF NOT EXISTS `mtrl_joint`( 
                                         `id_joint`    INT(3) NOT NULL AUTO_INCREMENT, 
                                          # разные поля 
                                         `sh`          INT(2) DEFAULT NULL, 
                                         `id_goods`    INT(3) NULL, #id товара   
                                         PRIMARY KEY(`id_joint`), 
                                         KEY `sh`(`sh`) 
                                         FOREIGN KEY (`id_goods`) REFERENCES `mtrl_goods`(`id_goods`) 
                                           ON DELETE CASCADE, 
                                         FOREIGN KEY (`sh`) REFERENCES `mtrl_goods`(`sh`) 
                                           ON UPDATE CASCADE 
                                       )ENGINE=InnoDB DEFAULT CHARSET="utf8"; 
Родительская таблица "mtrl_goods", дочерняя "mtrl_joint"

Таблицы создаются и всё работает без проблем, за одним исключением

Если я удаляю или обновляю запись (поле sh) в родительской таблице, то в дочерней записи удаляются или обновляются записи (поле sh).
Если я удаляю записи в дочерней таблице, то удаление проходит без ошибок, но если я пытаюсь обновить поле sh у одной из записей, то возникает ошибка "1452 Cannot add or update a child row: a foreign key constraint fails"

Подскажите, как можно сделать так, что бы в дочерней таблице можно было обновлять поле sh у отдельно взятой записи, но при этом при обновлении записи в родительской таблице обновлялось поле sh у всех записей в дочерней?
Аватара пользователя
solo
Сообщения: 320
Зарегистрирован: 2013.03.08, 19:59
Откуда: Ukraine, VINNITSA

Re: Обновление по внешнему ключу

Сообщение solo »

Несколько замечаний.
1) Никто не именует id_goods и id_joint, а просто id
2) Тип int(3) тоже бред есть для этого TINYINT (от -128 до 127) и SMALLINT (от -32 768 до 32 767 ) или UNSIGNED (без знаковый тип)
3) Связь таблиц делают единожды (одно поле первой табл на одно поле другой)
4) Почитай о связующей таблице
5) И вообще советую тебе почитать или посмотреть курс по mysql например( [Специалист] Проектирование и создание баз данных на MySQL 5 [2010, RUS])
Аватара пользователя
SiZE
Сообщения: 2813
Зарегистрирован: 2011.09.21, 12:39
Откуда: Perm
Контактная информация:

Re: Обновление по внешнему ключу

Сообщение SiZE »

solo писал(а):Несколько замечаний.
1) Никто не именует id_goods и id_joint, а просто id
2) Тип int(3) тоже бред есть для этого TINYINT (от -128 до 127) и SMALLINT (от -32 768 до 32 767 ) или UNSIGNED (без знаковый тип)
3) Связь таблиц делают единожды (одно поле первой табл на одно поле другой)
4) Почитай о связующей таблице
5) И вообще советую тебе почитать или посмотреть курс по mysql например( [Специалист] Проектирование и создание баз данных на MySQL 5 [2010, RUS])
Ты видимо оттуда все это нахватал? Кто как хочет то так и именует, вапще пофиг.
Аватара пользователя
SiZE
Сообщения: 2813
Зарегистрирован: 2011.09.21, 12:39
Откуда: Perm
Контактная информация:

Re: Обновление по внешнему ключу

Сообщение SiZE »

slo_nik писал(а):Если я удаляю записи в дочерней таблице, то удаление проходит без ошибок, но если я пытаюсь обновить поле sh у одной из записей, то возникает ошибка "1452 Cannot add or update a child row: a foreign key constraint fails"

Подскажите, как можно сделать так, что бы в дочерней таблице можно было обновлять поле sh у отдельно взятой записи, но при этом при обновлении записи в родительской таблице обновлялось поле sh у всех записей в дочерней?
Так нельзя сделать. Это ограничение целостности, суть его явно связывать таблицы, а не выборочно случайным образом. Есть два выхода: первый и самый оптимальный, написать тригер, второй похуже, контролировать изменения в коде.

Какая задача у тебя?
Аватара пользователя
solo
Сообщения: 320
Зарегистрирован: 2013.03.08, 19:59
Откуда: Ukraine, VINNITSA

Re: Обновление по внешнему ключу

Сообщение solo »

SiZE писал(а):
solo писал(а):Несколько замечаний.
1) Никто не именует id_goods и id_joint, а просто id
2) Тип int(3) тоже бред есть для этого TINYINT (от -128 до 127) и SMALLINT (от -32 768 до 32 767 ) или UNSIGNED (без знаковый тип)
3) Связь таблиц делают единожды (одно поле первой табл на одно поле другой)
4) Почитай о связующей таблице
5) И вообще советую тебе почитать или посмотреть курс по mysql например( [Специалист] Проектирование и создание баз данных на MySQL 5 [2010, RUS])
Ты видимо оттуда все это нахватал? Кто как хочет то так и именует, вапще пофиг.
Ты хочешь сказать что я не прав?
И если ты пишешь о триггерах объясни ему что на хостинге он ими не воспользуется.
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Re: Обновление по внешнему ключу

Сообщение slo_nik »

SiZE писал(а):
slo_nik писал(а):Если я удаляю записи в дочерней таблице, то удаление проходит без ошибок, но если я пытаюсь обновить поле sh у одной из записей, то возникает ошибка "1452 Cannot add or update a child row: a foreign key constraint fails"

Подскажите, как можно сделать так, что бы в дочерней таблице можно было обновлять поле sh у отдельно взятой записи, но при этом при обновлении записи в родительской таблице обновлялось поле sh у всех записей в дочерней?
Так нельзя сделать. Это ограничение целостности, суть его явно связывать таблицы, а не выборочно случайным образом. Есть два выхода: первый и самый оптимальный, написать тригер, второй похуже, контролировать изменения в коде.

Какая задача у тебя?
Задача в следующем.
Есть товары и поставщики. На каждый товар может быть несколько поставщиков, у каждого своя скидка на этот товар. При добавлении товара в базу, кроме данных самого товара(цена,вес,характеристики...), указываются поставщики(название фирмы и скидка на товар). Все эти данные заносятся как раз в таблицу `mtrl_joint`, только id. Ничего, кроме цифр, в этой таблице быть не должно.
Добавили товар в базу. В таблице товаров будет одна запись, в mtrl_join может быть несколько записей принадлежащих товару.
Обновляем поле sh товару, в таблице joint обновится это же поле у всех записей принадлежащих товару.
Удаляем товар - удаляются все записи из joint принадлежащие этому товару.
С этим вопросов нет.
Удаляем конкретную запись из таблицы joint, она удаляется успешно, не затрагивая сам товар, соседние записи и не выдаёт ошибки.
Если обновляем поле sh конкретной записи в таблице joint, то выдаёт ошибку, которую я написал выше.
Мне надо, чтобы при обновлении поля sh в таблице joint не возникало этой ошибки.
Я не могу просто понять, почему удаление проходит без ошибки, а вот обновление с ошибкой?
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Re: Обновление по внешнему ключу

Сообщение slo_nik »

solo писал(а):Несколько замечаний.
1) Никто не именует id_goods и id_joint, а просто id
2) Тип int(3) тоже бред есть для этого TINYINT (от -128 до 127) и SMALLINT (от -32 768 до 32 767 ) или UNSIGNED (без знаковый тип)
3) Связь таблиц делают единожды (одно поле первой табл на одно поле другой)
4) Почитай о связующей таблице
5) И вообще советую тебе почитать или посмотреть курс по mysql например( [Специалист] Проектирование и создание баз данных на MySQL 5 [2010, RUS])
Вот по первому пункту я с Вами не согласен. Может "Специалист" так не именует поля, но не думаю, что так должны делать все...

Что касается пункта 3. Получается, что не могу связать таблицы по двум полям?
Аватара пользователя
solo
Сообщения: 320
Зарегистрирован: 2013.03.08, 19:59
Откуда: Ukraine, VINNITSA

Re: Обновление по внешнему ключу

Сообщение solo »

slo_nik писал(а):Что касается пункта 3. Получается, что не могу связать таблицы по двум полям?
Ты конечно можешь вязать все что угодно но есть как бы какие то правила (Нормальная форма)
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Re: Обновление по внешнему ключу

Сообщение slo_nik »

Блогадарю за ссылку, но что-то не получается разобраться самостоятельно.
Вы можете мне пальцем ткнуть, где моя ошибка?
Изменил немного структуру таблиц и внешних ключей(правда изначально так было)

Код: Выделить всё

CREATE TABLE IF NOT EXISTS `mtrl_goods`(
                                         `id_goods` INT(3) NOT NULL AUTO_INCREMENT,
                                         `sh`       INT(2) DEFAULT 0,
                                         PRIMARY KEY(`id_goods`,`sh`)
                                       )ENGINE=InnoDB DEFAULT CHARSET="utf8";

CREATE TABLE IF NOT EXISTS `mtrl_joint`(
                                         `id_joint`    INT(3) NOT NULL AUTO_INCREMENT,
                                         `sh`          INT(2) DEFAULT 0,
                                         `id_goods`    INT(3) NULL, #id товара
                                         `create`      DATETIME DEFAULT '0000-00-00 00:00:00',
                                         PRIMARY KEY(`id_joint`),
                                         INDEX (`id_goods`,`sh`),
                                         FOREIGN KEY (`id_goods`, `sh`) REFERENCES `mtrl_goods`(`id_goods`, `sh`)
                                           ON DELETE CASCADE
                                           ON UPDATE CASCADE
                                       )ENGINE=InnoDB DEFAULT CHARSET="utf8";
 
Всё осталось по прежнему, удаление из дочерней таблицы идёт без ошибки, а вот обновление выдаёт ошибку
Аватара пользователя
solo
Сообщения: 320
Зарегистрирован: 2013.03.08, 19:59
Откуда: Ukraine, VINNITSA

Re: Обновление по внешнему ключу

Сообщение solo »

Как бы сделал я.

Код: Выделить всё

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `product` (`id`, `name`) VALUES
(1, 'Мыло'),
(2, 'Шампунь'),
(3, 'Гель для душа');

CREATE TABLE IF NOT EXISTS `product_supplier` (
  `product_id` int(10) unsigned NOT NULL DEFAULT '0',
  `supplier_id` smallint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`,`supplier_id`),
  KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

INSERT INTO `product_supplier` (`product_id`, `supplier_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(1, 2),
(2, 2),
(3, 2);

CREATE TABLE IF NOT EXISTS `supplier` (
  `id` smallint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `supplier` (`id`, `name`) VALUES
(1, 'Oriflame'),
(2, 'Procter & Gamble');

ALTER TABLE `product_supplier`
  ADD CONSTRAINT `product_supplier_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `product_supplier_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Re: Обновление по внешнему ключу

Сообщение slo_nik »

solo, благодарю за пример, пытаюсь разобраться в нём, но мне постоянно выдаёт ошибку "1064", почему - не пойму...
Постоянно ругается на строку

Код: Выделить всё

ALTER TABLE `mtrl_joint` ADD CONSTRAINT `mtrl_joint_idfk_2` FOREIGN KEY `id_supplier` REFERENCES `mtrl_supplier` (`id_supplier`) ON DELETE CASCADE;
ALTER TABLE `mtrl_joint` ADD CONSTRAINT `mtrl_joint_idfk_1` FOREIGN KEY `id_goods` REFERENCES `mtrl_goods` (`id_goods`) ON DELETE CASCADE ON UPDATE CASCADE;
Именно на этот участок

Код: Выделить всё

REFERENCES `mtrl_supplier` (`id_supplier`) ON DELETE CASCADE
Пожалуйста, помогите уже до конца, ткните носом, что я не так написал? Пересмотрел строку несколько раз, вроде все кавычки, пробелы на месте, но ошибки не вижу...

P.S. Всё, вроде ошибки исправил, буду дальше разбираться...)))
Аватара пользователя
solo
Сообщения: 320
Зарегистрирован: 2013.03.08, 19:59
Откуда: Ukraine, VINNITSA

Re: Обновление по внешнему ключу

Сообщение solo »

Код: Выделить всё

FOREIGN KEY (`id_supplier`)  FOREIGN KEY (`id_goods`)
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Re: Обновление по внешнему ключу

Сообщение slo_nik »

solo, благодарю за очередную подсказку.

В общем с Вашей помощью у меня получилось следующее
При удалении из таблиц поставщиков или товара происходит удаление из общей таблицы `mtrl_joint`(блин, как её правильно обозвать?)
Структура получилась такая (лишние поля убрал)

Код: Выделить всё

CREATE TABLE IF NOT EXISTS `mtrl_goods`(
                                         `id_goods` INT(5) NOT NULL AUTO_INCREMENT,
                                         `goods`    VARCHAR(50) NULL,
                                         PRIMARY KEY(`id_goods`)
                                       )ENGINE=InnoDB DEFAULT CHARSET="utf8";

CREATE TABLE IF NOT EXISTS `mtrl_joint`(
                                         `id_joint`    INT(10) NOT NULL AUTO_INCREMENT,
                                         `id_category` INT(3) NULL, #id категории товара
                                         `parent_id`   INT(3) NULL, #id root категории товара
                                         `id_supplier` INT(3) NULL, #id поставщика
                                         `id_goods`    INT(3) NULL, #id товара
                                         PRIMARY KEY(`id_joint`),
                                         KEY `id_goods` (`id_goods`),
                                         KEY `id_supplier` (`id_supplier`)
                                       )ENGINE=InnoDB DEFAULT CHARSET="utf8";

CREATE TABLE IF NOT EXISTS `mtrl_supplier`(
                                            `id_supplier` INT(5) NOT NULL AUTO_INCREMENT,
                                            `supplier`    VARCHAR(150) NULL,
                                            PRIMARY KEY(`id_supplier`)
                                          )ENGINE=InnoDB DEFAULT CHARSET='utf8';

    ALTER TABLE `mtrl_joint`
      ADD CONSTRAINT `mtrl_joint_ibfk_2` FOREIGN KEY (`id_supplier`) REFERENCES `mtrl_supplier` (`id_supplier`) ON DELETE CASCADE,
      ADD CONSTRAINT `mtrl_joint_ibfk_1` FOREIGN KEY (`id_goods`) REFERENCES `mtrl_goods` (`id_goods`) ON DELETE CASCADE;
Но теперь потребовалось добавить связь с таблицей категорий товара

Код: Выделить всё

CREATE TABLE IF NOT EXISTS `mtrl_categories`(
                                             `id_category` INT(2) NOT NULL AUTO_INCREMENT,
                                             `category`    VARCHAR(30) NULL,
                                             `parent_id`   INT(3) NULL,
                                             PRIMARY KEY(`id_category`)
                                            )ENGINE=InnoDB DEFAULT CHARSET='utf8';
 
В одной таблице у меня хранятся и родительские и дочернии категории.

Добавил связь, в таблице `mtrl_joint` дописал

Код: Выделить всё

KEY `id_goods` (`id_category`),
 
и добавил команду

Код: Выделить всё

ADD CONSTRAINT `mtrl_joint_ibfk_3` FOREIGN KEY (`id_category`) REFERENCES `mtrl_categories` (`id_category`) ON DELETE CASCADE,
 
По идее, если удаляешь категорию(дочернюю), то из общей таблицы должна удалиться запись, но этого не происходт...
В чём мой очередной косяк?
Аватара пользователя
solo
Сообщения: 320
Зарегистрирован: 2013.03.08, 19:59
Откуда: Ukraine, VINNITSA

Re: Обновление по внешнему ключу

Сообщение solo »

Учи матчасть, а если не хочется тогда делай через php.
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Re: Обновление по внешнему ключу

Сообщение slo_nik »

solo писал(а):Учи матчасть, а если не хочется тогда делай через php.
Учу по мере возможностей...
Но если Вы видите, в чём моя ошибка в данном случае, могли бы и подсказать, а не отвечать ради количества ответов...

p.s. На брудершафт мы с Вам не пили... Это так, к размышлению...
Аватара пользователя
solo
Сообщения: 320
Зарегистрирован: 2013.03.08, 19:59
Откуда: Ukraine, VINNITSA

Re: Обновление по внешнему ключу

Сообщение solo »

Не в количестве комментов дело. Просто тема mysql очень интересная и нужная. И помнится мне что пока не изучил доку по mysql, php mysqli, pdo и сборке mysql на серваке дело было дрянь. Так что желаю ВАМ скорее разобраться самому и тогда будет озарение.
slo_nik
Сообщения: 344
Зарегистрирован: 2013.10.07, 19:08

Re: Обновление по внешнему ключу

Сообщение slo_nik »

solo писал(а):Не в количестве комментов дело. Просто тема mysql очень интересная и нужная. И помнится мне что пока не изучил доку по mysql, php mysqli, pdo и сборке mysql на серваке дело было дрянь. Так что желаю ВАМ скорее разобраться самому и тогда будет озарение.
Благодарю за пожелание...

Но пока въедешь во все тонкости, а сделать надо сейчас) Примеры в сети однотипные, не сразу разберёшься, что к чему, поэтому и спрашиваю на конкретном примере.
Ответить