mysql – 基于附加列实施外键约束

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 基于附加列实施外键约束，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3522字，纯文字阅读大概需要6分钟。

内容图文

我有以下业务规则：

>帐户可以拥有零个或多个许可证.
>每个许可证都有一个独特的指南.
>每个许可证都预先分配给给定的帐户.
>所有安装都属于给定帐户.
>所有商业安装都需要许可证.
>没有两个commercial_installations可以使用相同的许可证.
>可以删除commercial_installation,然后可以在新的commercial_installation上使用许可证.
> commercial_installation只能使用已分配给安装帐户的许可证.

我该如何执行最后一条规则？ commercial_installation只能使用已分配给安装帐户的许可证.或者换句话说,对于给定的guid存储在commercial_installations中,licenses.accounts_id必须等于installed.accounts_id.请参阅底部的示例数据.

CREATE TABLE IF NOT EXISTS accounts (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  otherData VARCHAR(45) NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS licenses (
  guid CHAR(36) NOT NULL,
  accounts_id INT NOT NULL,
  otherData VARCHAR(45) NULL,
  PRIMARY KEY (guid),
  INDEX fk_licenses_accounts1_idx (accounts_id ASC),
  CONSTRAINT fk_licenses_accounts1
    FOREIGN KEY (accounts_id)
    REFERENCES accounts (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS installations (
  id INT NOT NULL AUTO_INCREMENT,
  accounts_id INT NOT NULL,
  otherData VARCHAR(45) NULL,
  PRIMARY KEY (id),
  INDEX fk_installations_accounts1_idx (accounts_id ASC),
  CONSTRAINT fk_installations_accounts1
    FOREIGN KEY (accounts_id)
    REFERENCES accounts (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS open_installations (
  installations_id INT NOT NULL,
  otherData VARCHAR(45) NULL,
  PRIMARY KEY (installations_id),
  CONSTRAINT fk_open_installations_installations1
    FOREIGN KEY (installations_id)
    REFERENCES installations (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS commercial_installations (
  installations_id INT NOT NULL,
  licenses_guid CHAR(36) NOT NULL,
  otherData VARCHAR(45) NULL,
  PRIMARY KEY (installations_id),
  UNIQUE INDEX fk_commercial_installations_licenses1_idx (licenses_guid ASC),
  CONSTRAINT fk_commercial_installations_installations1
    FOREIGN KEY (installations_id)
    REFERENCES installations (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_commercial_installations_licenses1
    FOREIGN KEY (licenses_guid)
    REFERENCES licenses (guid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

给出以下示例数据：

> b5060518-f87e-4acc-82c8-adb5750685a9和d6f23460-0d77-400e-ae96-13f436e40245只能存在于commercial_installations.id为1或2的commercial_installations中.
>同样,8739ef62-7fff-4913-81de-3d00e8f50ecb为3.
>同样,36cc0787-5cb9-4c3a-b79d-1dcfb83d2794为4.

账户

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | Account 1 |
|  2 | Account 2 |
|  3 | Account 3 |
|  4 | Account 4 |
+----+-----------+

许可证

+--------------------------------------+-------------+
|                 guid                 | accounts_id |
+--------------------------------------+-------------+
| b5060518-f87e-4acc-82c8-adb5750685a9 |           1 |
| d6f23460-0d77-400e-ae96-13f436e40245 |           1 |
| 36cc0787-5cb9-4c3a-b79d-1dcfb83d2794 |           2 |
| 8739ef62-7fff-4913-81de-3d00e8f50ecb |           3 |
+--------------------------------------+-------------+

安装

+----+-------------+
| id | accounts_id |
+----+-------------+
|  1 |           1 |
|  2 |           1 |
|  3 |           3 |
|  4 |           2 |
+----+-------------+

解决方法:

当然,您可以在应用程序的逻辑中强制执行它,但我想您希望在数据库级别执行此操作.

我在这里看到的唯一选项是为INSERT,UPDATE和DELETE操作添加触发器.普通外键不会为您执行此操作.

而且我喜欢这个问题,因为我之前遇到过这个问题而且没有真正花时间在SO中正式询问它.

内容总结

以上是互联网集市为您收集整理的mysql – 基于附加列实施外键约束全部内容，希望文章能够帮你解决mysql – 基于附加列实施外键约束所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端