問題描述

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

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.

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