wake-up-neo.com

Einschränkungen für MySQL-Fremdschlüssel, Kaskadenlöschung

Ich möchte Fremdschlüssel verwenden, um die Integrität zu erhalten und Waisen zu vermeiden (ich verwende bereits innoDB).

Wie mache ich eine SQL-Anweisung, die auf CASCADE LÖSCHEN?

Wenn ich eine Kategorie lösche, wie stelle ich sicher, dass keine Produkte gelöscht werden, die sich auch auf andere Kategorien beziehen.

Die Pivot-Tabelle "categories_products" erstellt eine Viele-zu-Viele-Beziehung zwischen den beiden anderen Tabellen.

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

products
- id
- name
- price

categories_products
- categories_id
- products_id
150
Cudos

Wenn durch Ihre Kaskadierung ein Produkt gelöscht wird, weil es Mitglied einer Kategorie war, die getötet wurde, haben Sie Ihre Fremdschlüssel nicht ordnungsgemäß eingerichtet. In Anbetracht Ihrer Beispieltabellen sollten Sie die folgenden Tabellen einrichten:

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;

Auf diese Weise können Sie ein Produkt löschen OR= eine Kategorie, und nur die zugehörigen Datensätze in categories_products gehen daneben verloren. Die Kaskade wandert nicht weiter in den Baum hinein und löscht die übergeordnete Produkt-/Kategorietabelle .

z.B.

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

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

Wenn Sie die Kategorie 'rot' löschen, stirbt nur der Eintrag 'rot' in der Kategorietabelle sowie die beiden Einträge prod/cats: 'rote Stiefel' und 'rote Mäntel'.

Der Löschvorgang wird nicht weiter kaskadiert und die Kategorien "Stiefel" und "Mäntel" werden nicht entfernt.

kommentarverfolgung:

sie verstehen immer noch nicht, wie kaskadierte Löschvorgänge funktionieren. Sie wirken sich nur auf die Tabellen aus, in denen die "on delete cascade" definiert ist. In diesem Fall wird die Kaskade in der Tabelle "categories_products" festgelegt. Wenn Sie die rote Kategorie löschen, werden in categories_products nur Datensätze mit category_id = red Kaskadenweise gelöscht. Es werden keine Datensätze berührt, in denen 'category_id = blue' ist, und es wird nicht zur Tabelle "products" weitergeleitet, da in dieser Tabelle kein Fremdschlüssel definiert ist.

Hier ist ein konkreteres Beispiel:

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
+------------+-------------+

Angenommen, Sie löschen Kategorie 2 (blau):

DELETE FROM categories WHERE (id = 2);

das DBMS überprüft alle Tabellen, bei denen ein Fremdschlüssel auf die Tabelle "Kategorien" zeigt, und löscht die Datensätze mit der übereinstimmenden ID 2. Da wir die Fremdschlüsselbeziehung nur in products_categories definiert haben, beenden Sie Nach Abschluss des Löschvorgangs mit dieser Tabelle fortfahren:

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

In der Tabelle products ist kein Fremdschlüssel definiert, sodass die Kaskade dort nicht funktioniert. Sie haben also immer noch Stiefel und Fäustlinge aufgelistet. Es gibt einfach keine "blauen Stiefel" und keine "blauen Fäustlinge" mehr.

368
Marc B

Die Antwort auf diese Frage hat mich verwirrt und ich habe einen Testfall in MySQL erstellt. Ich hoffe, das hilft

-- 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
Abderrahim

Ich glaube (da bin ich mir nicht sicher), dass Fremdschlüsseleinschränkungen nicht genau das tun, was Sie bei Ihrem Tischdesign wollen. Am besten definieren Sie eine gespeicherte Prozedur, mit der eine Kategorie wie gewünscht gelöscht wird, und rufen diese Prozedur dann auf, wenn Sie eine Kategorie löschen möchten.

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

Sie müssen der Verknüpfungstabelle auch die folgenden Fremdschlüsseleinschränkungen hinzufügen:

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

Die CONSTRAINT-Klausel kann natürlich auch in der CREATE TABLE-Anweisung vorkommen.

Nachdem Sie diese Schemaobjekte erstellt haben, können Sie eine Kategorie löschen und das gewünschte Verhalten erzielen, indem Sie CALL DeleteCategory(category_ID) eingeben (wobei category_ID die zu löschende Kategorie ist). Setzen Sie jedoch keine normale DELETE FROM - Abfrage ab, es sei denn, Sie möchten mehr Standardverhalten (d. H. Sie löschen nur aus der Verknüpfungstabelle und lassen die Tabelle products in Ruhe).

8
Hammerite