I'm currently writing code to update an entities child collection (one-to-many relationship), and while thinking about how to write code to determine which entities have been added/removed/modified, I realized that not only is it easier to just recreate the whole list, but probably better for SQL server performance?
Let's say I have a table
Student with a one-to-many relationship to
Course, and I have there 2 courses Math and Physics, with primary keys 1 and 2 respectively, and obviously a foreign key to Student which is 1.
If I one day decide to update the first course, remove the other, and add a new one, I would end up with the following entities
- Math (upated), primary key is: 1
Physics (deleted), primary key was: 2
- History (added), primary key is: 5000
Now the 2 courses are no longer next to each other, and this probably causes big performance problems in the long run since my keys are now fragmented, is this correct?
A related question is whether I should have a primary key on the
Course table at all? I usually add it by default to all of my (except association) tables, but in this case I never query courses individually, but always in the context of a student. Does it make sense to keep the primary key, even if I know I'll never reference it from anywhere? Would the lack of a primary key hurt performance?
EDIT: I've decided to keep the primary key, in-case in the future I do need to query this child table without the parent.
My primary keys are integers and have a clustered index (default on SQL Server) and I'm using SQL Server 2016, and Entity Framework 6 as my ORM.
Here's my CREATE TABLE from SSMS. I'm using EF Code-First so I didn't write any of this by hand.
CREATE TABLE [dbo].[Courses]( [Id] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](max) NOT NULL, [StudentId] [uniqueidentifier] NOT NULL, [Fk1Id] [int] NOT NULL, [Fk2Id] [int] NOT NULL, [Fk3Id] [int] NULL, CONSTRAINT [PK_dbo.Courses] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Courses] WITH CHECK ADD CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId] FOREIGN KEY([StudentId]) REFERENCES [dbo].[Students] ([Id]) GO ALTER TABLE [dbo].[Courses] CHECK CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId] GO --Omitted other alter tables for Fk1Id,Fk2Id, and Fk3Id
StudentId is the foreign key that I will always use to fetch these rows, they are not relevant in any other context (at least for now).
I have renamed the table and column names for privacy reasons, and removed 3 foreign key columns for simplicity! I hope they're not too relevant.