Mysql on delete cascade1/15/2024 ![]() The length of string types need not be the same. ![]() The size and the signedness of integer types have to be the same. It depends on the MySQL version.Ĭorresponding columns in the foreign key and the referenced key must have similar internal data types so that they can be compared without a type conversion. MySQL does not always auto-create indexes on foreign keys or referenced keys: sometimes you have to create them explicitly. If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error).No matter what table type (of those supporting FK's) are used for defining the FK relationship/constraint, both tables involved in the relationship have to be same type and there must be an index where the foreign key and the referenced key are listed as the first columns. If your database has no password, remove the -u username -ppassword part. There is no space between the option -p and the password.OR for table in `mysql -u root -ppassword -s -N -e "use old_db show tables from old_db "` do mysql -u root -ppassword -s -N -e "use old_db rename table old_db.$table to new_db.$table " done You will need to adjust the permissions after that.įor scripting in a shell, you can use either of the following: mysql -u username -ppassword old_db -sNe 'show tables' | while read table \ĭo mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table" done There's just no 'blue boots' and no 'blue mittens' anymore.įor InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database: RENAME TABLE old_db.table TO new_db.table 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. Since we only defined the foreign key relationship in products_categories, you end up with this table once the delete completes: +-+-+ 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. Let's say you delete category #2 (blue): DELETE FROM categories WHERE (id = 2) Here's a more concrete example: categories: products: ![]() 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. If you delete the 'red' category, the only records that will cascade delete in categories_products are those where category_id = red. In this case, the cascade is set in the "categories_products" table. They only affect the tables in which the "on delete cascade" is defined. You're still misunderstanding how cascaded deletes work. The delete will not cascade any farther and will not take out the 'boots' and 'coats' categories. 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'. Prod/cats: red boots, green mittens, red coats, black hats products: boots, mittens, hats, coatsĬategories: red, green, blue, white, black The cascade won't travel farther up the tree and delete the parent product/category table.Į.g. This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. Given your example tables, you should have the following table setup: CREATE TABLE categories (įOREIGN KEY (category_id) REFERENCES categories (id)įOREIGN KEY (product_id) REFERENCES products (id) 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.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |