Asked  7 Months ago    Answers:  5   Viewed   61 times

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).

How do I make a SQL statment that DELETE ON CASCADE?

If I delete a category then how do I make sure that it would not delete products that also are related to other categories.

The pivot table "categories_products" creates a many-to-many relationship between the two other tables.

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

 Answers

63

If your cascading deletes nuke a product because it was a member of a category that was killed, then you've set up your foreign keys improperly. Given your example tables, you should have the following table setup:

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;

This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. The cascade won't travel farther up the tree and delete the parent product/category table.

e.g.

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

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

If you delete the 'red' category, then only the 'red' entry in the categories table dies, as well as the two entries prod/cats: 'red boots' and 'red coats'.

The delete will not cascade any farther and will not take out the 'boots' and 'coats' categories.

comment followup:

you're still misunderstanding how cascaded deletes work. They only affect the tables in which the "on delete cascade" is defined. In this case, the cascade is set in the "categories_products" table. If you delete the 'red' category, the only records that will cascade delete in categories_products are those where category_id = red. It won't touch any records where 'category_id = blue', and it would not travel onwards to the "products" table, because there's no foreign key defined in that table.

Here's a more concrete example:

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

Let's say you delete category #2 (blue):

DELETE FROM categories WHERE (id = 2);

the DBMS will look at all the tables which have a foreign key pointing at the 'categories' table, and delete the records where the matching id is 2. Since we only defined the foreign key relationship in products_categories, you end up with this table once the delete completes:

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

There's no foreign key defined in the products table, so the cascade will not work there, so you've still got boots and mittens listed. There's just no 'blue boots' and no 'blue mittens' anymore.

Tuesday, June 1, 2021
 
Xatoo
answered 7 Months ago
85

The foreign key column(s) must reference column(s) comprising a left-most prefix of the primary key or a unique key in the parent table.

In other words, the following examples work in InnoDB:

CREATE TABLE Foo ( a INT, b INT, c INT, PRIMARY KEY (a,b,c) );
CREATE TABLE Bar ( x INT, y INT );

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(b,c); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(a,c); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(a,b); -- RIGHT

ALTER TABLE Bar ADD FOREIGN KEY (x) REFERENCES Foo(b); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x) REFERENCES Foo(a); -- RIGHT

You got an error because you're trying to do the equivalent of (x) references Foo(b).
Your column codmenuitem is the second of three columns in the primary key of the parent.

It would work if smenuitememp.codemenuitem were to reference smenuitem.codmodulo, because that column is the leftmost column in the parent table's primary key.


Re your followup question:

Keep in mind the way foreign keys work. Every time you insert or update a row in the child table, it needs to look up a row in the parent table to verify that the value exists in the referenced column. If the column isn't indexed, it'll have to do a table-scan to achieve this lookup, and that would be very expensive, assuming your parent table grows.

If you try to look up a row based on the middle column of a multi-column index, the index doesn't help you. By analogy, it's like searching a telephone book for all people with a certain middle name.

Standard ANSI SQL requires that the referenced column be part of a PRIMARY KEY or UNIQUE KEY, and it requires that the foreign key columns match all the columns of a primary or unique constraint in the parent.

But InnoDB is more permissive. It still requires that the referenced column in the parent table be indexed so the lookup can be efficient, and that the referenced columns be the leftmost in the index. But a non-unique index is okay; it's allowed for a foreign key to reference it.

This can lead to weird cases like a child row that references more than one row in the parent, but it's expected that you will handle such anomalies.


I feel the need to emphasize the last point. You will get anomalous data if you define foreign keys to non-uniquely indexed columns in the parent. This will probably cause your queries to report rows multiple time when you do joins. You should not use this behavior of InnoDB; you should define foreign keys only to parent columns that are unique.

Saturday, May 29, 2021
 
Sidarta
answered 7 Months ago
92

Foreign key constraints with on delete cascade are supported, but you need to enable them.
I just added the following to my SQLOpenHelper, which seems to do the trick.

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    if (!db.isReadOnly()) {
        // Enable foreign key constraints
        db.execSQL("PRAGMA foreign_keys=ON;");
    }
}

I declared my referencing column as follows.

mailbox_id INTEGER REFERENCES mailboxes ON DELETE CASCADE
Sunday, June 6, 2021
 
ClmentM
answered 7 Months ago
81

The type of the field in a foreign key must be the same as the type of the column they're referencing. You have the following (snipping):

CREATE  TABLE IF NOT EXISTS `state` (
  `state_id` INT NOT NULL AUTO_INCREMENT ,
...
CREATE  TABLE IF NOT EXISTS `brand` (
  `brand_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
...
CREATE  TABLE IF NOT EXISTS `location` (
...
  `state_id` TINYINT UNSIGNED NULL DEFAULT NULL ,
...
  `brand_id` TINYINT UNSIGNED NOT NULL ,

so you're trying to refer to INT fields (in tables state and brand) with TINYINT fields in table location. I think that's the error it's complaining about. Not sure how it came up in the first place, or why zeroing out FOREIGN_KEY_CHECKS doesn't stop MySQL from diagnosing the error, but what happens if you fix this type mismatch?

Saturday, September 18, 2021
 
Pratik
answered 3 Months ago
16

Yes, if you have a VARCHAR(512) column on the referencing table, the data will exist twice.

I recommend that you make the referencing table's foreign key refer to an integer primary key for the first table, not the 512-byte data. This is kind of what normalization is all about.

Thursday, November 11, 2021
 
toesslab
answered 4 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share