在 MySQL 中使用条件值检查进行约束

2021-09-14 00:00:00 constraints mysql unique-constraint unique-index unique-key

我需要添加一个基于组合和值的约束检查

I need to add a combinational and value based Constrain check

我的表结构是

CREATE TABLE IF NOT EXISTS `ContactPhone` (
  `ContactPhoneId` int(10) unsigned NOT NULL,
  `ContactId` int(11) NOT NULL,
  `PhoneId` smallint(5) unsigned NOT NULL,
  `Status` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ContactPhone`
--
ALTER TABLE `ContactPhone`
  ADD PRIMARY KEY (`ContactPhoneId`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ContactPhone`
--
ALTER TABLE `ContactPhone`
  MODIFY `ContactPhoneId` int(10) unsigned NOT NULL
  AUTO_INCREMENT,AUTO_INCREMENT=1;

我需要根据以下组合条件约束来确保记录

I need to ensure the record based on the following combinational conditional constrain

  • ContactId
  • PhoneId
  • Status == 1(值应该是 1 - Active Record)
  • ContactId
  • PhoneId
  • Status == 1 (value should be 1 - Active Record)

注意:不需要非活动记录(即，Status == 0).我只需要对 Active Records 进行此约束.

Note: No need of the In-active Records (i.e., Status == 0). I need this constrain only for the Active Records.

请帮助我如何为上述移动的 MySQL 表添加此约束.

Kindly assist me how to add this constrain for the above motioned MySQL Table.

推荐答案

根据 文档、

对于可以包含 NULL 的列，UNIQUE 索引允许多个 NULL 值.

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

因此从 Status 中删除 not null-约束并在 (ContactId,PhoneId,Status) 上添加唯一索引将像您一样工作想要它，如果您使用 null 而不是 0 来表示 inactive 记录.

So removing the not null-constraint from Status and adding a unique index on (ContactId,PhoneId,Status) will work as you want it to, if you use null instead of 0 for inactive records.

如果您不想或不能为您的 Status 列使用 null，请确保 Status=0 和 Status=null 行为相同，或例如也想将 Status=2 视为 active(并强制执行唯一性)，您可以添加一个将根据 Status 计算的虚拟列.

If you don't want to or cannot use null for your Status column, want to make sure both Status=0 and Status=null behave identically, or e.g. want to treat Status=2 as active (and enforcing uniqueness) too, you can add a dummy column that will be calculated from Status.

如果您使用的是 MySQL 5.7+，则可以使用生成的列执行此操作:

If you are using MySQL 5.7+, you can do this with a generated column: 

CREATE TABLE IF NOT EXISTS `ContactPhone` (
  `ContactPhoneId` int(10) unsigned NOT NULL auto_increment primary key,
  `ContactId` int(11) NOT NULL,
  `PhoneId` smallint(5) unsigned NOT NULL,
  `Status` tinyint(1) NOT NULL DEFAULT '1',
  `StatusUnq` tinyint(1) as (if(Status <> 0, 1, null)) stored null,
  constraint unique (ContactId, PhoneId, StatusUnq)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (1, 1, 1, 1);
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (2, 1, 1, 1);
-- Duplicate key error 
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (3, 1, 1, 0);
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (4, 1, 1, 0);
update ContactPhone set Status = 1 where ContactPhoneId = 4;
-- Duplicate key error

否则，您可以使用普通列并使用触发器来计算列的值，例如:

Otherwise, you can use a normal column and use triggers to calculate the value of the column, e.g.:

create trigger trbi_contactPhoneUnique before insert on ContactPhone 
for each row
  set new.StatusUnq = if(new.Status <> 0, 1, null);

create trigger trbu_contactPhoneUnique before update on ContactPhone 
for each row
  set new.StatusUnq = if(new.Status <> 0, 1, null);

您当然可以将公式切换为例如if(new.Status <> 0, new.Status, null); 如果您也想允许 Status 的不同值.

You can of course switch the formula to e.g. if(new.Status <> 0, new.Status, null); if you want to allow different values of Status too.

相关文章