引用多列的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上的单个列索引上没有值。因此，您将看到下面描述这一点的架构。

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

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

原因如下。元组作为一个组存在。把它想象成一个集合的实例，它作为一个组具有意义。将其与单独部分的存在进行比较，就会有不同之处。不是因为用户的存在，而是因为有一个以元组作为存在的用户行。

1. 使用一种或另一种有什么利弊？

在最后一段中已经描述了这些优点:作为一个元组存在于user表中的一个实际分组。

它们是苹果和橘子，用于不同的用途。

1. 这个用例到底是什么？(主要问题)

用例将要求元组作为一个组存在，而不是单个项的存在。它被用于所谓的合成。特别是合成FK。以我的Here的答案为例。

简而言之，当您想要执行特殊的难以想象的解决方案时，需要在其他实体的组合级别(分组)上要求引用完整性(RI)。许多人认为这是不可能的，所以他们首先想到的是TRIGGER的执行或前端的强制执行。幸运的是，这些用例可以通过FK复合材料来实现，从而使RI保持在db级别(而不是在前端)。

附录

从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;

此模式的亮点可归结为UNIQUE KEY中的tupleHolder表、device中的FK、GRANT限制(未显示授权)，以及设备被屏蔽以避免在tupleHolder中进行愚蠢的编辑，因为，如前所述：

• 赠款
• FK必须得到尊重，这样tupleHolder就不会被搅乱

如果tupleHolder被搅乱(3个contacts ids)，那么FK就会被违反。

换句话说，它与设备中基于单个列的设备(称为device.badIdea INT )具有FK的方式不同，它将FK返回到tupleHolder.id。

此外，正如前面提到的，这与仅仅拥有contacts存在不同。相反，重要的是contacts的组合是否存在，它是一个元组。在我们的例子中，元组已经被审查，并且有一个信用等级，并且在那个元组中的id不能在购买设备之后被搅乱，除非有足够的许可。即使如此，FK也已经就位了。

它可能需要15分钟的时间，但有很大的不同。

我希望这能帮到你。