I was creating some new tables and populating them when I ran into this not so straight forward error:
Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.
I was confused at first, I had set the primary key to auto increment using the IDENTITY property yet it was throwing an error. I scratched my head for a few minutes before noticing my not so obvious mistake — I had confused my foreign key with the primary key.
The two fields only had a one letter difference and I had accidentally typed the name for my primary key column when I had meant to type in the other one. As a result it was trying to set the value of an identity field, which is not allowed.
After a quick correction to my insert query I was back in business.
A seasoned Senior Solutions Architect with 18 years of experience in technology design and implementation. Renowned for innovative solutions and strategic insights, he excels in driving complex projects to success. Outside work, he is a passionate fisherman and fish keeper, specializing in planted tanks.