1

We had a requirement where a two character key was to be used as a primary key (natural key) instead of a surrogate key for a look up table in sql server 2012 database. The two characters were not supposed to change, and thus was the choice for primary key. Second reason was that the number of records will not exceed probably 10.

However a question has now been raised to us if it is possible to edit them (via the user interface) without having to delete the record (which is the current setup). this key is also a foreign key in three or more tables. Thus a delete is going to be detrimental and is currently being done only on a carefully considered basis.

Wondering if Entity Framework 6.0 and above now supports "OnUpdate Cascade" for primary keys? If not, is there a workaround for this? (other than deleting and creating the record again).

Please advise.

Sri Katte
  • 67
  • 7
  • IIRC, Entity Framework just plain does not support changing the primary key. It will *always* treat it as a delete+insert. I am not entirely sure of this, though, and maybe someone will post a useful workaround. –  Jun 09 '14 at 07:57
  • You should definitely drop this idea of an editable primary key. Introduce the surrogate key as quickly as you can. It's not only that EF won't let you do it, SQL Server doesn't allow it without jumping through hoops and loops. – Gert Arnold Jun 09 '14 at 08:30
  • @GertArnold SQL Server does allow it without jumping through any hoops. What SQL Server doesn't allow is changing `IDENTITY` columns, if that's what you meant, but that is not the case here, and independent of whether the column is part of any key. –  Jun 09 '14 at 09:43
  • @hvd You're right. But maybe only through loops, because you still have to turn foreigns keys off and on. – Gert Arnold Jun 09 '14 at 13:04
  • @GertArnold The OP already refers to `ON UPDATE CASCADE`, which can make SQL Server automatically update the value in any dependent tables too. –  Jun 09 '14 at 13:24
  • Are you referring to the following idea? 1.setup onupdate cascade on all the foriegn key relationships concerning this column. 2.handle updates via stored procedures so as to not confuse update on primary key for the EF? – Sri Katte Jun 09 '14 at 15:24

0 Answers0