Creating a constraint on two columns to support specific rule

Question

I have a table storing Device details. For simplicity, the columns are:

Id (Primary Key)
Name (varchar)
StatusId (Foreign Key to Status table).

The Status table has two columns:

Id (Primary Key)
State (varchar)

and two rows:

[Id | State]
  1 | Active
  2 | Inactive

I would like to allow multiple devices in the Devices table with the same Name, but only one of them can have status Active at any time.

That is to say, this should be allowed in the Devices table:

[Id | Name      | StatusId]
 10 | Mobile001 | 1
 11 | Mobile001 | 2
 12 | Mobile001 | 2
 20 | Tablet001 | 1
 21 | Tablet002 | 2
 22 | Tablet002 | 1
 23 | Tablet003 | 2

But this should not be allowed:

[Id | Name      | StatusId]
 10 | Mobile001 | 1    <-- wrong
 11 | Mobile001 | 1    <-- wrong
 12 | Mobile001 | 2    
 20 | Tablet001 | 1
 21 | Tablet002 | 1    <-- wrong
 22 | Tablet002 | 1    <-- wrong
 23 | Tablet003 | 2

Is there a way how to create a constraint in T-SQL to reject inserts and updates that violate this rule? And is there a way how to do it in EF code first using EntityTypeConfigurations and Fluent API, possibly via IndexAnnotation or IndexAttributes?

Thanks.

Use a filtered unique index. – Zohar Peled Mar 15 '18 at 17:00 — Zohar Peled, Mar 15 '18 at 17:00

score 1 · Answer 1 · answered Mar 15 '18 at 17:01

One method, as @ZoharPeled just commented is using a filtered unique index.

As you are only allowed one Active Device of a specific name, this can be implemented as below:

USE Sandbox;
GO
--Create sample table
CREATE TABLE Device (ID int IDENTITY(1,1),
                     [name] varchar(10),
                     [StatusID] int);
--Unique Filtered Index
CREATE UNIQUE INDEX ActiveDevice ON Device ([name], [StatusID]) WHERE StatusID = 1;
GO

INSERT INTO Device ([name], StatusID)
VALUES ('Mobile1', 1); --Works
GO
INSERT INTO Device ([name], StatusID)
VALUES ('Mobile1', 0); --Works
GO
INSERT INTO Device ([name], StatusID)
VALUES ('Mobile2', 1); --Works
GO
INSERT INTO Device ([name], StatusID)
VALUES ('Mobile1', 1); --Fails
GO
UPDATE Device
SET StatusID = 1
WHERE ID = 2; --Also fails
GO
SELECT *
FROM Device;
GO
DROP TABLE Device;

Any questions, please do ask.

score 0 · Answer 2 · answered Mar 15 '18 at 17:05

0

In EF CF You could achieve it by setting an unique index like described in this answer.

modelBuilder.Entity<Device>()
    .HasIndex(d => new { d.Name, d.StatusId })
    .IsUnique();

answered Mar 15 '18 at 17:05

ChW

3,168
2
21
34

Creating a constraint on two columns to support specific rule

2 Answers2