MySQL 聯合索引實戰：員工-部門多對多關係防重複插入

業務場景：員工與部門多對多，中間表 employee_dept 需保證 (employee_id, dept_id) 唯一，防止重複關聯。解法：組合唯一索引。

表結構

-- 部門表
CREATE TABLE `dept` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptname` CHAR(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 員工表
CREATE TABLE `employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 關聯表
CREATE TABLE `employee_dept` (
  `id` INT(11) NOT NULL,
  `employeeid` INT(11) NOT NULL,
  `deptid` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `bb` (`deptid`),
  UNIQUE KEY `myindex` (`employeeid`, `deptid`),  -- 關鍵：組合唯一索引
  CONSTRAINT `aa` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`id`),
  CONSTRAINT `bb` FOREIGN KEY (`deptid`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

測試資料

INSERT INTO `dept` VALUES ('1', '技術部'), ('2', '產品部');
INSERT INTO `employee` VALUES ('1', '張三');

-- 正常關聯
INSERT INTO `employee_dept` VALUES ('1', '1', '1');
INSERT INTO `employee_dept` VALUES ('2', '1', '2');

-- 重複關聯 → 報錯
INSERT INTO `employee_dept` VALUES ('3', '1', '1');
-- ERROR 1062 (23000): Duplicate entry '1-1' for key 'myindex'

關鍵點總結

1. UNIQUE KEY myindex (employeeid, deptid) 同時實現「查詢加速」與「業務唯一性約束」
2. 順序：高選擇性列優先（員工 ID 通常比部門 ID 選擇性高）
3. 外鍵約束 CONSTRAINT 維護引用完整性，索引 KEY bb (deptid) 加速反向查詢
4. 應用層捕獲 1062 錯誤碼，友好提示「已關聯」

延伸：MySQL 8.0+ 隱形索引驗證

ALTER TABLE employee_dept ALTER INDEX myindex INVISIBLE;
-- 觀察業務是否報錯、效能是否下降
ALTER TABLE employee_dept ALTER INDEX myindex VISIBLE;

組合唯一索引是多對多關聯表防重的標準解法，簡潔、高效、資料庫層面強一致性。