INNODB 级联删除和更新 [英] INNODB cascade on delete and update

问题描述

我正在尝试在 mysql 数据库中创建一些表来处理客户，将他们分配到组并为这些组中的客户提供唯一的促销代码/优惠券.

I'm trying to create some tables in a mysql db to handle customers, assign them to groups and give customers within these groups unique promotion codes/coupons.

有 3 个父(?)表 - 客户、组、促销然后我有表 - customerGroups 将每个 customer_id 分配给许多 group_id我也有 - customerPromotions 将每个 customer_id 分配给许多 Promotion_id

there are 3 parent(?) tables - customers, groups, promotions then I have table - customerGroups to assign each customer_id to many group_id's also I have - customerPromotions to assign each customer_id to many promotion_id's

我知道我需要在删除和更新时使用级联，这样当我删除客户、促销或组时，数据也会从子表中删除.我整理了一些 php 来轻松创建表格 http://pastebin.com/gxhW1PGL

I know I need to use cascade on delete and update so that when I delete a customer, promotion or group the data is also removed from the child tables. I put together some php to create the tables easily http://pastebin.com/gxhW1PGL

我一直在尝试阅读级联外键引用，但我认为通过尝试做事情然后了解它们的工作原理，我会学得更好.任何人都可以给我他们的意见，我应该如何处理这些表以使它们正常运行.

I've been trying to read up on cascade, foreign key references but I think I learn better by trying to do things then learning why they work. Can anyone please give me their input on what I should do to these tables to have them function correctly.

我希望在开始查询或进一步操作之前正确设置数据库和表，以便任何建议都很好.

I would like to have the database and tables set up correctly before I start with queries or anything further so any advice would be great.

推荐答案

您似乎只需要一点指导.所以我会尽量简短.

You seem to want just a little guidance. So I'll try to be brief.

$sql = "CREATE TABLE customerGroups (
   customer_id int(11) NOT NULL,
   group_id int(11) NOT NULL,
   PRIMARY KEY (customer_id, group_id),
   CONSTRAINT customers_customergroups_fk
     FOREIGN KEY (customer_id)
     REFERENCES customers (customer_id) 
     ON DELETE CASCADE,
   CONSTRAINT groups_customergroups_fk
     FOREIGN KEY (group_id)
     REFERENCES groups (group_id) 
     ON DELETE CASCADE
)ENGINE = INNODB;";

当身份难以确定时，您只需要 id 号.当您与人打交道时，很难确定身份.有很多人叫约翰·史密斯".

You only need id numbers when identity is hard to nail down. When you're dealing with people, identity is hard to nail down. There are lots of people named "John Smith".

但是您正在处理两件已经确定的事情.(并用身份证号码标识，所有事情.)

But you're dealing with two things that have already been identified. (And identified with id numbers, of all things.)

级联删除是有道理的.对 id 号进行级联更新的情况相对较少；他们被认为永远不会改变.(Oracle DBA 坚持主键必须是 ID 号，并且它们绝不能改变的主要原因是因为 Oracle 不能级联更新.)如果以后某些 ID 号需要因任何原因而改变，您可以更改表以包含 ON UPDATE CASCADE.

Cascading deletes makes sense. It's relatively rare to cascade updates on id numbers; they're presumed to never change. (The main reason Oracle DBAs insist that primary keys must always be ID numbers, and that they must never change is because Oracle can't cascade updates.) If, later, some id numbers need to change for whatever reason, you can alter the table to include ON UPDATE CASCADE.

$sql = "CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(50) NOT NULL UNIQUE,
group_desc varchar(140),
PRIMARY KEY (group_id)
)ENGINE = INNODB;";

注意 group_title 上的附加唯一约束.您不想在您的数据库中允许这样的事情(如下).

Note the additional unique constraint on group_title. You don't want to allow anything like this (below) in your database.

group_id  group_title
--
1         First group
2         First group
3         First group
...
9384      First group

您会希望在所有表格中进行这些更改.(可能除了您的客户表.)

You'll want to carry those kinds of changes through all your tables. (Except, perhaps, your table of customers.)

这篇关于INNODB 级联删除和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！