問題描述

我需要添加一個(gè)基于組合和值的約束檢查

I need to add a combinational and value based Constrain check

我的表結(jié)構(gòu)是

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;

我需要根據(jù)以下組合條件約束來確保記錄

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

• ContactId
• PhoneId
• Status == 1(值應(yīng)該是 1 - Active Record)

• ContactId
• PhoneId
• Status == 1 (value should be 1 - Active Record)

注意:不需要非活動(dòng)記錄(即，Status == 0).我只需要對(duì) Active Records 進(jìn)行此約束.

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

請(qǐng)幫助我如何為上述移動(dòng)的 MySQL 表添加此約束.

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

推薦答案

根據(jù) 文檔、

對(duì)于可以包含 NULL 的列，UNIQUE 索引允許多個(gè) 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，請(qǐng)確保 Status=0 和 Status=null 行為相同，或例如也想將 Status=2 視為 active(并強(qiáng)制執(zhí)行唯一性)，您可以添加一個(gè)將根據(jù) Status 計(jì)算的虛擬列.

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+，則可以使用生成的列執(zhí)行此操作:

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 

否則，您可以使用普通列并使用觸發(fā)器來計(jì)算列的值，例如:

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

您當(dāng)然可以將公式切換為例如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.

這篇關(guān)于在 MySQL 中使用條件值檢查進(jìn)行約束的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！