Comp Sci: SQL Server tables: can’t insert an integer ID (primary key)?

Self-tutoring about computer science: the tutor mentions a bump he hit and the investigation he performed because of it.

Today I was using SQL Server and decided to experiment: I changed an outside program to set the integer id (which is also the primary key) during an insert query. Before, I’d allowed the database to set each new row’s id – which is typical. I made the Insert query from Linq, using Visual Basic. My other queries from that context have all worked. In fact, I’m impressed with Linq, but I guess that’s another story for another post.

I wasn’t surprised when the insert that included the new id didn’t work; databases have rules. From one to another, they may not always be the same, and there’s many of them, in total. However, most of the rules, I’ll admit, you won’t even notice if you just follow common sense. Setting the id from outside the database isn’t necessarily advised, unless you’re aware what you’re doing: it’s safer for the database to just not let you try.

The typical, charming way that a database decides if you know what you’re doing, and should therefore be allowed to circumvent a rule, is very simple. If you really know the database, you’ll know the rule is the default, and how to unset it. That knowledge, however, is usually much less common than “create, read, update or delete” – only someone savvy enough to understand the rule and why it’s there is likely to know how to unset it.

I didn’t change the default rule; I just changed the database table back to auto-setting each new id, then omitted the id from the Insert query. Since, it works as expected.

For any dot net programmer doing Linq queries who arrives at this post, I’ll share with you what I found:

1. It seems that the boolean that prevents the user from setting the primary key integer id is IDENTITY_INSERT. Its default is FALSE aka OFF. I’ve only read about it.
2. The search phrase “microsoft docs identity insert” gives me the answers I would need.

Source:

stackoverflow.com

docs.microsoft.com