CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkef-code-firstentity-framework-6
Rudey
Rudey

Reputation: 4965

Can I have a primary key/unique constraint on multiple properties that are unique in any order?

I have the following model that's essentially a many-to-many relationship table.

public class Friend
{
    [Key, ForeignKey("UserA")]
    public int UserAId { get; set; }
    public virtual User UserA { get; set; }

    [Key, ForeignKey("UserB")]
    public int UserBId { get; set; }
    public virtual User UserB { get; set; }

    public DateTime Since { get; set; }

    ...
}

Friendships are always two-way in my application. How can I constrain my backend from inserting both new Friend { UserAId = 1, UserBId = 2 } and new Friend { UserAId = 2, UserBId = 1 }?

Upvotes: 2

Views: 97

Answers (1)

Slava Utesinov
Slava Utesinov

Reputation: 13498

I think you have the only one solution: to create manually constraint on your table (you can add migration and then write something like this):

public override void Up()
{
    Sql(@"ALTER TABLE dbo.Friends ADD 
    CONSTRAINT CustomOrder CHECK (UserAId > UserBId)");
}

public override void Down()
{
    Sql(@"ALTER TABLE dbo.Friends DROP CONSTRAINT CustomOrder");
}

And now you can add only user's id pairs like that: 2 - 1, 5 - 3, but not 1 - 2 and 3 -5, so we have not duplicates as you wanted, but also you must provide assigning to UserA id value that will be more then UserB id value, but I think that it is not difficult for you.

Upvotes: 1

Related Questions