mysql - MySQL外键引用多列的用法

Question

我只是偶然发现了 MySQL 外键引用多个列的可能性。我想知道如下所示的多列外键的主要目的是什么

ALTER TABLE `device` 
ADD CONSTRAINT `fk_device_user`
  FOREIGN KEY (`user_created_id` , `user_updated_id` , `user_deleted_id`)
  REFERENCES `user` (`id` , `id` , `id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

我的问题是

1. 和创建三个独立的外键一样吗？
2. 使用一种或另一种有什么优点/缺点吗？
3. 这个的确切用例是什么？(主要问题)

Answer 1

1. 和创建三个独立的外键一样吗？

没有。请考虑以下事项。

首先，将其视为 (id,id,id) 是没有用的，实际上是 (id1,id2,id3)。因为 (id,id,id) 的元组对于 id 上的单个列索引没有任何值(value)。因此，您将看到下面描述该内容的架构。

create schema FKtest001;
use FKtest001;

create table user
(   id int auto_increment primary key,
    fullname varchar(100) not null,
    id1 int not null,
    id2 int not null,
    id3 int not null,
    index `idkUserTuple` (id1,id2,id3)
);

create table device
(   id int auto_increment primary key,
    something varchar(100) not null,
    user_created_id int not null,
    user_updated_id int not null,
    user_deleted_id int not null,
    foreign key `fk_device_user` (`user_created_id` , `user_updated_id` , `user_deleted_id`)
       REFERENCES `user` (`id1` , `id2` , `id3`)

);
show create table device;
CREATE TABLE `device` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `something` varchar(100) NOT NULL,
   `user_created_id` int(11) NOT NULL,
   `user_updated_id` int(11) NOT NULL,
   `user_deleted_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_device_user` (`user_created_id`,`user_updated_id`,`user_deleted_id`),
   CONSTRAINT `device_ibfk_1` FOREIGN KEY (`user_created_id`, `user_updated_id`, `user_deleted_id`) REFERENCES `user` (`id1`, `id2`, `id3`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
show indexes from device; -- shows 2 indexes (a PK, and composite BTREE)
-- FOCUS heavily on the `Seq_in_index` column for the above

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

drop table device;
drop table user;

create table user
(   id int auto_increment primary key,
    fullname varchar(100) not null,
    id1 int not null,
    id2 int not null,
    id3 int not null,
    index `idkUser1` (id1),
    index `idkUser2` (id2),
    index `idkUser3` (id3)
);

create table device
(   id int auto_increment primary key,
    something varchar(100) not null,
    user_created_id int not null,
    user_updated_id int not null,
    user_deleted_id int not null,
    foreign key `fk_device_user1` (`user_created_id`)
       REFERENCES `user` (`id1`),
    foreign key `fk_device_user2` (`user_updated_id`)
       REFERENCES `user` (`id2`),
    foreign key `fk_device_user3` (`user_deleted_id`)
       REFERENCES `user` (`id3`)
);
show create table device;
CREATE TABLE `device` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `something` varchar(100) NOT NULL,
   `user_created_id` int(11) NOT NULL,
   `user_updated_id` int(11) NOT NULL,
   `user_deleted_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_device_user1` (`user_created_id`),
   KEY `fk_device_user2` (`user_updated_id`),
   KEY `fk_device_user3` (`user_deleted_id`),
   CONSTRAINT `device_ibfk_1` FOREIGN KEY (`user_created_id`) REFERENCES `user` (`id1`),
   CONSTRAINT `device_ibfk_2` FOREIGN KEY (`user_updated_id`) REFERENCES `user` (`id2`),
   CONSTRAINT `device_ibfk_3` FOREIGN KEY (`user_deleted_id`) REFERENCES `user` (`id3`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 show indexes from device; -- shows 4 indexes (a PK, and 3 indiv FK indexes)
-- FOCUS heavily on the `Seq_in_index` column for the above

那里有 2 个部分。 show indexes from device 将在顶部显示维护的 2 个索引的差异。在底部，维护了4个指标。如果由于某种原因顶部的索引元组对系统有用，那么元组方法肯定是可行的方法。

原因如下。元组作为一个组存在。将其视为具有组意义的集合实例。将其与单独部分的单纯存在进行比较，就会有所不同。并不是说用户存在，而是存在一个包含该元组的用户行。

1. 使用一种或另一种有什么优点/缺点吗？

上文最后一段描述了优点:作为元组存在于 user 表中的实际分组。

它们是苹果和橙子，用途不同。

1. 它的确切用例是什么？ (主要问题)

用例是需要元组作为一个组存在的东西，而不是单个项目的存在。它用于所谓的合成。特别是合成 FK。请参阅我的这个答案 Here举个例子。

简而言之，当您想要实现特别难以想到的解决方案时，需要在其他实体的复合级别(分组)上引用完整性 (RI)。很多人觉得做不到所以首先想到的是TRIGGER执行或者前端执行。幸运的是，这些用例可以通过 FK Composites 实现，从而将 RI 留在它应该在的数据库级别(而不是在前端)。

附录

OP 要求提供比上面的链接更好的真实示例。

考虑以下架构:

CREATE SCHEMA testRealLifeTuple;
USE testRealLifeTuple;

CREATE TABLE contacts
(   id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(100) NOT NULL
    -- etc
);

CREATE TABLE tupleHolder
(   -- a tuple representing a necessary Three-some validation
    -- and vetting to get financing
    --
    -- If you can't vett these 3, you can't have my supercomputer financed
    --
    id INT AUTO_INCREMENT PRIMARY KEY,
    CEO INT NOT NULL,   -- Chief Executive Officer
    CFO INT NOT NULL,   -- Chief Financial Officer
    CIO INT NOT NULL,   -- Chief Geek
    creditWorthiness INT NOT NULL, -- 1 to 100. 100 is best

    -- the unique index is necessary for the device FK to succeed
    UNIQUE INDEX `idk_ContactTuple` (CEO,CFO,CIO), -- No duplicates ever. Good for re-use

    FOREIGN KEY `fk_th_ceo` (`CEO`) REFERENCES `contacts` (`id`),
    FOREIGN KEY `fk_th_cfo` (`CFO`) REFERENCES `contacts` (`id`),
    FOREIGN KEY `fk_th_cio` (`CIO`) REFERENCES `contacts` (`id`)
);

CREATE TABLE device
(   -- An Expensive Device, typically our Supercomputer that requires Financing.
    -- This device is so wildly expense we want to limit data changes
    --
    -- Note that the GRANTS (privileges) on this table are restricted.
    --
    id INT AUTO_INCREMENT PRIMARY KEY,
    something VARCHAR(100) NOT NULL,
    CEO INT NOT NULL,   -- Chief Executive Officer
    CFO INT NOT NULL,   -- Chief Financial Officer
    CIO INT NOT NULL,   -- Chief Geek
    FOREIGN KEY `fk_device_2_tuple` (`CEO` , `CFO` , `CIO`)
        REFERENCES `tupleHolder` (`CEO` , `CFO` , `CIO`)
    --
    -- Note that the GRANTS (privileges) on this table are restricted.
    --
);

DROP SCHEMA testRealLifeTuple;

这个模式的亮点归结为 tupleHolder 表中的 UNIQUE KEY，device 中的 FK，GRANT 限制(授权未显示)，以及设备不受 tupleHolder 中的 tomfoolery 编辑的影响，因为，如前所述:

• 赠款
• FK 必须得到尊重，所以 tupleHolder 不能乱用

如果 tupleHolder 被(3 个 contacts id)弄乱了，那么 FK 就会被违反。

换句话说，它绝不与具有基于设备中单个列的 FK 的设备相同，称之为 [device.badIdea INT]，这将 FK 返回到 tupleHolder。编号。

此外，如前所述，这与仅存在 contacts 不同。相反，重要的是 contacts 的组合存在，它是一个元组。在我们的例子中，元组已经过审查，并且具有信用评级，并且在购买设备后，该元组中的 ID 不能被弄乱，除非有足够的 GRANTS 允许这样做。即便如此，FK 已经到位。

可能需要 15 分钟才能完全理解，但两者之间存在巨大差异。

希望对您有所帮助。