Ограничения внешнего ключа MySQL, каскадное удаление

Я хочу использовать внешние ключи, чтобы сохранить целостность и избежать сирот (я уже использую innoDB).

Как сделать SQL statment, который удаляет на каскаде?

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

сводная таблица "categories_products" создает отношение "многие ко многим" между двумя другими таблицами.

categories
- id (INT)
- name (VARCHAR 255)

products
- id
- name
- price

categories_products
- categories_id
- products_id
3 134
mysql foreign-keys innodb

3 ответа:

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

CREATE TABLE categories (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE products (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
)Engine=InnoDB;

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

например

products: boots, mittens, hats, coats
categories: red, green, blue, white, black

prod/cats: red boots, green mittens, red coats, black hats

если вы удалите категорию "красный", то только запись "красный" в таблице категорий умирает, а также две записи prod/cats: "красные сапоги" и "красные пальто".

удаление не будет каскадировать дальше и не будет вынимать категории "сапоги" и "пальто".

комментарий последующих:

вы все еще не понимаете, как каскадные удаления работают. Они влияют только на таблицы, в которых " on delete cascade" определяемый. В этом случае каскад задается в таблице "categories_products". Если вы удалите категорию "red", единственные записи, которые будут каскадно удалять в categories_products, - это те, где category_id = red. Он не будет касаться никаких записей, где 'category_id = blue', и он не будет перемещаться вперед к таблице "продукты", потому что в этой таблице нет внешнего ключа, определенного в этой таблице.

вот более конкретный пример:

categories:     products:
+----+------+   +----+---------+
| id | name |   | id | name    |
+----+------+   +----+---------+
| 1  | red  |   | 1  | mittens |
| 2  | blue |   | 2  | boots   |
+---++------+   +----+---------+

products_categories:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 1          | 2           | // blue mittens
| 2          | 1           | // red boots
| 2          | 2           | // blue boots
+------------+-------------+

допустим, вы удалите категорию #2 (синий):

DELETE FROM categories WHERE (id = 2);

СУБД будет просматривать все таблицы, которые имеют внешний ключ, указывающий на таблицу "категории", и удалять записи, где соответствующий идентификатор равен 2. Поскольку мы только определили отношение внешнего ключа в products_categories, вы в конечном итоге с этой таблицей после удаления завершается:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 2          | 1           | // red boots
+------------+-------------+

нет внешнего ключа, определенного в products таблица, так что каскад не будет работать там, так что у вас все еще есть сапоги и варежки в списке. Там просто нет "синих сапог" и больше никаких "синих варежек".

349

Я запутался в ответе на этот вопрос, поэтому я создал тестовый случай в MySQL, надеюсь, это поможет

-- Schema
CREATE TABLE T1 (
    `ID` int not null auto_increment,
    `Label` varchar(50),
    primary key (`ID`)
);

CREATE TABLE T2 (
    `ID` int not null auto_increment,
    `Label` varchar(50),
    primary key (`ID`)
);

CREATE TABLE TT (
    `IDT1` int not null,
    `IDT2` int not null,
    primary key (`IDT1`,`IDT2`)
);

ALTER TABLE `TT`
    ADD CONSTRAINT `fk_tt_t1` FOREIGN KEY (`IDT1`) REFERENCES `T1`(`ID`) ON DELETE CASCADE,
    ADD CONSTRAINT `fk_tt_t2` FOREIGN KEY (`IDT2`) REFERENCES `T2`(`ID`) ON DELETE CASCADE;

-- Data
INSERT INTO `T1` (`Label`) VALUES ('T1V1'),('T1V2'),('T1V3'),('T1V4');
INSERT INTO `T2` (`Label`) VALUES ('T2V1'),('T2V2'),('T2V3'),('T2V4');
INSERT INTO `TT` (`IDT1`,`IDT2`) VALUES
(1,1),(1,2),(1,3),(1,4),
(2,1),(2,2),(2,3),(2,4),
(3,1),(3,2),(3,3),(3,4),
(4,1),(4,2),(4,3),(4,4);

-- Delete
DELETE FROM `T2` WHERE `ID`=4; -- Delete one field, all the associated fields on tt, will be deleted, no change in T1
TRUNCATE `T2`; -- Can't truncate a table with a referenced field
DELETE FROM `T2`; -- This will do the job, delete all fields from T2, and all associations from TT, no change in T1
11

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

CREATE PROCEDURE `DeleteCategory` (IN category_ID INT)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM
    `products`
WHERE
    `id` IN (
        SELECT `products_id`
        FROM `categories_products`
        WHERE `categories_id` = category_ID
    )
;

DELETE FROM `categories`
WHERE `id` = category_ID;

END

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

ALTER TABLE `categories_products` ADD
    CONSTRAINT `Constr_categoriesproducts_categories_fk`
    FOREIGN KEY `categories_fk` (`categories_id`) REFERENCES `categories` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `Constr_categoriesproducts_products_fk`
    FOREIGN KEY `products_fk` (`products_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE

предложение ограничения может, конечно, также появиться в CREATE Заявление таблицы.

создав эти объекты схемы, вы можете удалить категорию и получить желаемое поведение, выдав CALL DeleteCategory(category_ID) (где category_ID-это категория, которую нужно удалить), и она будет вести себя так, как вы хотите. Но не выдают нормальный DELETE FROM запрос, если вы не хотите более стандартного поведения (т. е. удалить только из таблицы ссылок и оставить таблица).

8