Change two rows simultaniously

Question

Context: ASP.NET MVC 2.0, Linq-to-Sql, .Net 3.5, IIS7, MS SQL 2008

I'm working on a game fan site.

I have a table as follows:

ToonId int (primary key)
RealmId (FK into Realms table)
OwnerId int (FK into Users table)
ToonName nvarchar(50)
IsMain bit

That is a single user may own multiple toons on multiple realms (aka servers), but exactly one toon must be marked as main per user per realm (only for realms where there is at least one toon).

Suppose I have two toons (on one realm): Foo (marked as main)
and Bar (not main)

I want to change my main, and for that I do something like: Foo.IsMain = false Bar.IsMain = true

I'm using Linq-to-SQL.

Question: how can I make said transition without entering a state when I have either more than one or zero main toons.

Note: I have a materialized view (filtered by IsMain = 1) that defines a composite key (OwnerId, RealmId), as such when I do main-toon transition I get "unique key violation" exception from that materialized view.

I have tried using transaction, but I still get exception on 'db.SubmitChanges()'.

Option 2: I suspect that I have the flaw in the database design (what's the name of this flaw?). Should I create a mapping table?

OwnerId, RealmId -> ToonId

Answer 1

Question 1

You need to use transactions.

 DBDataContext db = new DBDataContext();
 using (TransactionScope ts = new TransactionScope())
 {
     try
     {
         Toon toon1 = db.Toons.First(p => p.ToonId == 4);
         Toon toon2 = db.Toons.First(p => p.ToonId == 5);
         toon1.IsMain = false;
         toon2.IsMain = true;
         db.SubmitChanges();
         ts.Complete();
     }
     catch (Exception e)
     {
        Console.WriteLine(e.Message);
     }
}

• Transactions (LINQ to SQL)
• How to: Bracket Data Submissions by Using Transactions (LINQ to SQL)
• http://stackoverflow.com/questions/755796/how-to-create-a-linq-to-sql-transaction

Question 2

You need to think about your design a bit more.

Step 1

It is a player who has a main Toon so I would put a MainToonId on your Users table and remove your IsMain from your Toon table.

After this your tables would be Users ( Existing Stuff, MainToonId ), Realms ( Existing Stuff), Toons (ToonId, RealmId, OwnerId, ToonName )

Step 2

As you've suggested you could probably move the Realm/Owner/Toon link to its own table (or even ToonOwner and ToonRealm tables). The link information is related to Toon, but it's not an identifying part of being a Toon. The resolution of this "flaw" is normalisation and a database that needs it is described as "needing normalisation". However, this step is completely optional in this case and may be over-normalising for your needs.

If you did go with one matchup table, the new table would have a PK of the 3 values combined (if you are a natural keyer) otherwise a standard PK and a UNIQUE on the 3 values (if you are not a natural keyer). You should also have a UNIQUE on ToonId as each Toon can presumably only belong to one Realm or Owner.