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

Question

I have two tables in my database that I am trying to create for Linq2Sql objects. There they have more than that, but in essence they come down to:

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

The UserActivity table is, in fact, only a log of actions that the user performs against the "Rooms" table.

Since the UserActivity table UserActivity used only for logging the actions taken, it made little sense for me (at least for me) to create the primary key for the table initially, until the Linq2Sql developer refused to make the UserActivity part of the Room object in my Linq objects. When I set up the objects in the designer of Visual Studio, I received the following 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 an ActivityID column in my table, as shown above.

I would like to know if there is a way to allow Linq2Sql to create relationships between my objects without a primary key in both tables. If I do not have a primary key in the UserActivity table, objects can still be created, but relationships are not generated.

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

Answer 1

Any table that stores real data in your application should always have a primary key - in most cases, in SQL Server environments, INT IDENTITY (1,1) will be fine. You do not need to track them, do not need to keep accounts, etc. It doesn’t cost you much, it’s absolutely easy to do - I see no reason why you do 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 that a primary key is not needed are things like temporary tables for mass import of huge amounts of data or other temporary scenarios.

Mark

Answer 2

You need a primary key to create a relationship. It’s always good practice to create tables with primary keys, even if you add a surrogate (auto-increment).