I need to add a combinational and value based Constrain check
My Table Structure is
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
(value should be 1 - Active Record)Note: No need of the In-active Records (i.e., Status == 0
). I need this constrain only for the Active Records.
Kindly assist me how to add this constrain for the above motioned MySQL Table.
According to the documentation,
A UNIQUE index permits multiple NULL values for columns that can contain NULL.
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.
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
.
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);
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.