Unique index per Foreign Key

Question

I am looking for a way to perform some slightly more intelligent unique constraints on my EF Code First database table.

I have a two models in play; A Setting and a Tenant.

public class Setting
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
    public int TenantId { get; set; }
}

public class Tenant
{
    ///
    public int Id { get; set; }
    public virtual List<Setting> Settings { get; set; }
    ///
}

As you can probably guess, a Setting is linked to it's Tenant by the TenantId property which acts as the foreign key and match the Tenant's Id property.

What I want to achieve is to say that the Setting name must be unique per Tenant. For example, you can have a Setting named "SettingA" under "Tenant01" and a "SettingA" under "Tenant02" (each of which are two separate entities with here own Ids in the table, and can thus have different Values) and this be legal, but not allow for another Setting name "SettingA" to the Tenant's where it already exists.

I can see plenty of ways to just enforce straight uniqueness through the likes of database initialisation and checking when adding etc, but nothing on "conditional uniqueness".

if I understand well, this looks like a unique constraint on pair [Setting.Name, Setting.TenantId] ? — Raphaël Althaus, May 30 '13 at 14:30
I'm nearly 100% confident there is no structure to do this built-in, however you could build your own attribute to handle it. It would be fairly straight forward. — Mike Perrenoud, May 30 '13 at 14:31

score 3 · Accepted Answer · edited May 23 '17 at 10:25

3

It looks like an unique constraint on [Setting.Name, Setting.TenantId]

You should have validation rules in the "Code side", to avoid Sql errors on Save, and a UNIQUE CONSTRAINT on your db... to be consitent.

Code side

This can easily be achieved with a validation lib like FluentValidation, but you could also do this with custom attributes

Db side

With migrations

In up method :

CreateIndex("dbo.Setting", new[]{"TenantId", "Name"}, true, "Setting_UC");

In down method :

DropIndex("dbo.Setting", "Setting_UC");

Without migrations

see Unique Constraint in Entity Framework Code First

edited May 23 '17 at 10:25

Community

1
1

answered May 30 '13 at 14:40

Raphaël Althaus

59,727
6
96
122

That's a great start for me to be getting on with. Thank you kindly. =) – Kira Namida May 31 '13 at 12:11
Apparently: "Column 'Name' in table 'dbo.Settings' is of a type that is invalid for use as a key column in an index." I'll be looking into this myself but any thoughts? – Kira Namida May 31 '13 at 12:17
@KiraNamida yes, it's probably a `nvarchar(max)`. And `nvarchar(max)` is not accepted for indexes... You have to put a lower value (like `nvarchar(128)` for example. – Raphaël Althaus May 31 '13 at 12:25
Keeping this up to date. Solved that one by setting the string max length to 450 (max number of characters that equal the max number of bytes available). NOw moving on to solving: ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column. – Kira Namida May 31 '13 at 12:40
Update: This seems to be because of an existing constraint on the table that affects this column. Now to track it down and make sure it is removed. – Kira Namida May 31 '13 at 12:45
@KiraNamida in You we trust, you're on the way ! – Raphaël Althaus May 31 '13 at 12:54
I found the constraint, worked around it and now the database is throwing the exception I'm expecting when I break the constraint. Thanks for you advice, it got me where I needed to be. =3 – Kira Namida May 31 '13 at 13:43

Unique index per Foreign Key

1 Answers1