我刚刚发现了 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;
我的问题是
不是的。请考虑以下内容。
首先，把它看作（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个索引。如果由于某种原因，顶部部分的索引元组对系统有用，则该元组方法肯定是正确的选择。user表中作为元组实际分组的存在。TRIGGER执行或前端执行。幸运的是，这些用例可以通过FK Composites实现，从而使RI留在应该存在的db级别（永远不会在前端）。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 进行编辑。tupleHolder 被篡改（3个 contacts 的id），则将违反 FK。contacts 不同。相反，重要的是 contacts 的组成存在，即元组。 在我们的案例中，元组已经通过审核，并且具有信用评级，在购买设备后，其中的 id 不能被更改，除非具有足够的 GRANTS 权限。即使有，FK 也仍然存在。