mysql数据库中外键和触发器的基本用法(the basic use of foreign keys and triggers in mysql databases)

mysql数据库中外键和触发器的基本用法(the basic use of foreign keys and triggers in mysql databases)

我正在努力学习mySQL。 我正在使用mySQL workbench和Linux终端的混合物来解决它。 我正在努力使用外键和触发器。 我想我可能误解了其中的一个或两个，这就是造成混乱的原因。 我希望有人不能只提供解决问题的代码，但向我解释我的误解。

我有两个小桌子，我创建了一个名为Organization and People的小桌子。 在这种情况下，他们有一对多的关系：许多人可以为一个组织工作，但一个人只能为一个组织工作。 因此，我使用相同名称“organisation_id”将组织表主键'organisation_id'作为外键插入到people表中。 我这样做，以便我可以使用人员表中的外键来跟踪谁为哪个组织工作。 我的目标是当我向这个表添加新人时，使用触发器自动更新人员中的外键，从而跟踪谁对哪个组织工作。 我只是无法弄清楚如何做到这一点。 如果我在people表中添加一个条目，那么表格如何计算出它的行中缺少的单元格？ 我误解了一对多的关系和外键吗？ 任何人都可以告诉我如何正确编码并告诉我哪里出错了？ 我知道这可能是一个基本问题，但我需要帮助。

mySQL workbench的表格代码如下：

CREATE SCHEMA IF NOT EXISTS `learning` DEFAULT CHARACTER SET utf8 ; USE `learning` ; CREATE TABLE IF NOT EXISTS `learning`.`organisation` ( `organisation_id` INT UNSIGNED NOT NULL DEFAULT 1, `org_name` VARCHAR(45) NOT NULL, `org_website` VARCHAR(45) NULL, PRIMARY KEY (`organisation_id`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `learning`.`people` ( `people_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NULL, `job` VARCHAR(45) NULL, `organisation_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`people_id`), INDEX `fk_organisation_id_idx` (`organisation_id` ASC), CONSTRAINT `fk_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `learning`.`organisation` (`organisation_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

I'm working on learning mySQL. I'm using a mixture of mySQL workbench and Linux terminal to get my head around it. I'm struggling with foreign keys and triggers. I think I might have misunderstood one or both of these and that's what is causing the confusion. I'm hoping someone can not just provide code to fix the problem but explain to me what I'm misunderstanding.

I've got two small tables that I've created called Organisation and People. In this scenario they have a one to many relationship: many people can work for one organisation, but one person can only work for one organisation. I therefore insert the organisation tables primary key 'organisation_id' into the people table as a foreign key using the same name 'organisation_id'. I do this so that I can use the foreign key in the people table to keep track of who works for which organisation. My aim is then to have the foreign key in people update automatically using a trigger when I add new people to this table, thus keeping track of who works for which organisation. I just can't figure out how to do this. If I add an entry to the people table how can the table figure out the missing cell in it's row? Have I misunderstood one-to-many relations and foreign keys? Can anyone show me how to code this correctly and tell me where I've gone wrong? I know this is probably such a basic question but I need a help.

my tables code from mySQL workbench is below:

CREATE SCHEMA IF NOT EXISTS `learning` DEFAULT CHARACTER SET utf8 ; USE `learning` ; CREATE TABLE IF NOT EXISTS `learning`.`organisation` ( `organisation_id` INT UNSIGNED NOT NULL DEFAULT 1, `org_name` VARCHAR(45) NOT NULL, `org_website` VARCHAR(45) NULL, PRIMARY KEY (`organisation_id`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `learning`.`people` ( `people_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NULL, `job` VARCHAR(45) NULL, `organisation_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`people_id`), INDEX `fk_organisation_id_idx` (`organisation_id` ASC), CONSTRAINT `fk_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `learning`.`organisation` (`organisation_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

最满意答案

您正确理解了外键的概念，但没有理解它的应用。

是的，它可以帮助您将people与organisation关联起来，并确保您只能在people表中输入有效的organisation_id （有效=存在于organisation表中）。

但是，数据库无法自行解决人们所属的组织。 这是您需要在插入本身中提供的内容。

在使用此数据库的应用程序中，将有一个用于将people添加到数据库的表单。 在该表单上，您需要提供用户所属的组织。

外键定义中的on delete和on update子句实际上适用于organisation表：如果在organisation表中删除/更新了organisation_id字段，那么应该在people表中执行哪些操作。

You understood the concept of foreign keys correctly, but not its application.

Yes, it helps you to associate people with the organisation and makes sure that you can only enter a valid organisation_id into the people table (valid=exists in organisation table).

However, the database cannot figure it out on its own to which organisation a people belongs to. This is something you need to provide in the insert itself.

In the application that uses this database there would be a form for adding people to the database. On that form you need to provide which organisation the user belongs to.

The on delete and on update clauses in the foreign key definition actually apply to the organisation table: if the organisation_id field is deleted / updated in the organisation table, then what actions should be taken in the people table.