mysql - 用另一列查询的组合列的唯一约束

Question

为组合列添加表约束

我有一个包含下面几列的数据库表来存储联系方式

CREATE TABLE `contacts` (
  `id` int(11),
  `user_id` int(11), -- Foreign key to user table
  `contact_type` enum('Email', 'Phone', 'Address') -- Possible values
  `contact` varchar(100),
  `is_verified` tinyint(1),  -- Boolean
  `is_primary` tinyint(1), -- Boolean
  PRIMARY KEY (`id`)
);

是否可以对 is_primary 列设置约束，以便一个用户在每个 contact_type 上最多可以有一个主要联系人？

Answer 1

您可以添加唯一约束/索引来执行此操作:

create unique index unq_contacts_user_id_contact_type_is_primary
    on contact(user_id, contact_type, is_primary);

注意:这仅在 is_primary 取值“1”或 NULL 时有效。这个非常重要。否则，您最多只能联系一位“非主要”联系人和一位主要联系人。

在 Postgres(或 Oracle)中，您将使用过滤索引:

create unique index unqf_contacts_user_id_contact_type
    on contact(user_id, contact_type)
    where is_primary = 1; -- or however you specify that it is true