Linq2Sql: Can I create entities with foreign key relationships without a primary key in both tables?

Question

I have 2 tables in my database that I'm trying to create Linq2Sql entities for. There's more to them than this, but this is essentially what they come down to:

Rooms          UserActivity
--------       --------
RoomID         ActivityID 
               RoomID (foreign key on Rooms.RoomID)

The UserActivity table is essentially just a log for actions a user performs against the Rooms table.

Since the UserActivity table is only used for logging actions taken, it didn't make a lot of sense (to me at least) to create a primary key for the table originally, until the Linq2Sql mapper refused to make UserActivity a part of the Room entity in my Linq entities. When I set up the entities in the Visual Studio designer, I got these 2 warnings:

• Warning 1 DBML1062: The Type attribute 'UserActivity' of the Association element 'Room_UserActivity' of the Type element 'Room' does not have a primary key. No code will be generated for the association.
• Warning 2 DBML1011: The Type element 'UserActivity' contains the Association element 'Room_UserActivity' but does not have a primary key. No code will be generated for the association.

These warnings led me to create the ActivityID column in my table as displayed above.

What I'd like to know is if there is any way to allow Linq2Sql to create relationships between my entities without having a primary key in both tables. If I don't have the primary key in the UserActivity table, the entities can still be created, but the relationships aren't generated.

Is is it possible to do this, or should I try to make sure my tables always have a primary key in them as a general good practice?

Answer 1

You need a primary key to create relationships. It's good practise to always design tables with primary keys, even if you add surrogate (auto increment identity).

Answer 2

Any table that stores real data in your app should always have a primary key - most cases, in SQL Server environments, a INT IDENTITY(1,1) will be just fine. You don't have to keep track of those, no bookkeeping necessary etc. It doesn't cost you much, totally easy to do - I don't see any reason why not have a primary key, even on your UserActivity table.

ALTER TABLE UserActivity
  ADD UserActivityID INT IDENTITY(1,1) 
  CONSTRAINT PK_UserActivity PRIMARY KEY

and you're done!

The only time I would say no primary key is needed is for things like temporary tables when bulk importing huge amounts of data, or other temporary scenarios.

Marc