设置MySQL外键和索引-迁移到InnoDB

Question

我需要帮助设置迁移到InnoDB的外键。情况是，我有3个表:工作、客户和联系人。

作业属于客户，作业属于联系人。一个客户有很多工作，有很多联系人。一个联系人属于一个客户，并且有多个工作。我需要在作业表中设置外键的帮助，以便只插入属于选定客户的联系人。(即，如果客户A具有联系人A1和A2，而客户B具有联系人B1，则一旦您选择客户A作为职务的客户，它将拒绝A1或A2以外的联系人条目)。这可以用外键实现吗?或者我必须用我选择的编程语言进行验证吗？

这是我的方案：

CREATE TABLE jobs(
  job_id INT(11) NOT NULL AUTO_INCREMENT,
  customer_id INT(11) DEFAULT NULL,
  contact_id INT(11) DEFAULT NULL,
  job_number INT(11) UNSIGNED NOT NULL DEFAULT 0,
  status_void TINYINT(1) DEFAULT 0,
  PRIMARY KEY (job_id),
  INDEX active_jobs (job_number, status_void),
  INDEX customer_id (customer_id),
  UNIQUE INDEX job_number (job_number),
  CONSTRAINT FK_jobs_contacts_contact_id FOREIGN KEY (contact_id)
  REFERENCES contacts (contact_id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT FK_jobs_customers_customer_id FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id) ON DELETE SET NULL ON UPDATE CASCADE
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;`

CREATE TABLE customers(
  customer_id INT(11) NOT NULL AUTO_INCREMENT,
  inactive TINYINT(1) DEFAULT 0,
  customer_name VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY (customer_id),
  UNIQUE INDEX customer_name (customer_name),
  INDEX inactive (inactive)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE contacts(
  contact_id INT(11) NOT NULL AUTO_INCREMENT,
  customer_id INT(11) DEFAULT NULL,
  inactive TINYINT(1) DEFAULT 0,
  first_name VARCHAR(50) DEFAULT NULL,
  last_name VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (contact_id),
  UNIQUE INDEX contact_key (customer_id, last_name, first_name),
  INDEX customer_id (customer_id),
  INDEX inactive (inactive),
  INDEX name (last_name, first_name),
  CONSTRAINT fk_contacts_customers_customer_id FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

我的另一个目标是设置索引，以列出所有不是无效的作业(status_void=0)和所有不活动的客户和联系人(inactive=0)。我不知道在这种情况下，是使用多列索引(Job)还是使用两个单独的索引(customers/contacts)会有帮助。

我还在学习，请不要对我太客气。谢谢您抽时间见我。

Answer 1

这可以用外键来做吗?或者我必须用我选择的编程语言进行验证吗？

是的，是的。

这是FKs添加到数据中的引用完整性。但是，如果您没有在您的应用程序中考虑到这一点，您将呈现一种可怕的用户体验，这将允许用户输入不属于某个客户的联系人，并在将联系人提交到数据库后收到错误。最好在你的应用程序中说明这一点，以防止这种情况被发送到数据库，而FK是一种备份，以防以某种方式通过。

我的另一个目标是设置索引，以列出所有未失效的作业(status_void=0)和未处于非活动状态的所有客户和联系人(inactive=0)。我不知道在这种情况下，是使用多列索引(Job)还是使用两个单独的索引(customers/contacts)会有帮助。

要构建的索引往往是特定于查询的。因此，您可能希望显示一个示例查询，以便最好地在索引中获得建议，而不仅仅是您的最终目标是什么。重要的是要知道，一个查询中的每个表引用只能使用一个索引。因此，如果一个表在where子句中有多个过滤器，那么您往往会通过使用多列索引来获得最佳性能。

在规划索引时，最好考虑哪些列具有较少的唯一值，这将过滤出更多的行，并在索引中首先列出这些行。

话虽如此，如果没有代表性的查询和explain语句的结果，局外人就不能在索引方面向您提供太多建议，因为它们的有效性取决于它们所针对的数据。