0

In a SQL Server database, I have a table with three columns. I am using Entity Framework in the project for the database operations. This table doesn't require an ID column as it will be not be referenced by other tables.

But it has two foreign key constraints and a primary key with the combination of these two foreign keys. This is how I want the data to be stored in the table.

For example, a business can acquire a county, so business id is one foreign key and county id is another foreign key. Combination of these two foreign keys is the primary key as the business cannot acquire the same county multiple times. One of the functionality in the application allows the user to do transfer, at that time I want to update the business id that is part of the primary key.

On calling context.SaveChanges, the code is throwing an error

The property 'business_id' is part of the object's key information and cannot be modified.

My code:

CREATE TABLE [dbo].[BusinessCounty]
(
    [BusinessId] [int] NOT NULL,
    [CountyId] [smallint] NOT NULL,
    [Active] [tinyint] NULL,

    CONSTRAINT [PK_10_3] 
        PRIMARY KEY NONCLUSTERED ([BusinessId] ASC, [CountyId] ASC)
)
GO

ALTER TABLE [dbo].[BusinessCounty] 
    ADD CONSTRAINT [FK_11_4] 
        FOREIGN KEY([BusinessId]) REFERENCES [dbo].[Business] ([ID])
GO

ALTER TABLE [dbo].[BusinessCounty]  
    ADD CONSTRAINT [FK_15_3] 
        FOREIGN KEY([CountyId]) REFERENCES [dbo].[County] ([ID])
GO

Please advice how I can update this key.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cindy
  • 35
  • 8

1 Answers1

1

This table doesn't require an ID column as it will be not be referenced by other tables.

This assumption is false. An ID column does not exist, so other tables can reference it. The most basic purpose of an ID is to act as a clustering key, and even if you don't define an explicit ID for your table, one would be internally generated.

In my opinion, it is a good practice to define a single-column Primary Key for your table... your scenario is an example, of the many scenarios where you run into problem with a table not having a single-column ID... and the solution is to define one.

I think this question would be helpful.

Hooman Bahreini
  • 14,480
  • 11
  • 70
  • 137
  • ok, i understand every table needs to have a primary key and i can define one here. But to achieve the uniqueness of business and county combination, should i define the unique constraint? – Cindy Apr 18 '19 at 02:11