Negative Primary Keys
Like others have said, the database is fine with this.
But it would be a problems for a .NET application that uses DataSet+DataAdapter as they use negative keys as temporaries for new records.
Other data-access layers may use similar tricks.
Why is negative id or zero considered a bad practice?
To be clear, this question and answer are about using negative numbers for surrogate keys, not for natural keys.
As far as I know, there are three reasons for considering it to be a bad practice.
- It violates the principle of least surprise.
- Some people assume all ID numbers are non-negative.
- Some people use negative numbers to indicate errors.
The first one has some validity to it. You never see SQL examples or answers on SO that use negative ID numbers. (I'm going to change that, starting today.)
The second and third are corollaries to the first, in that programmers often assume surprise-free behavior. (That reminds me of discovering that VBA would let me multiply two dates, returning a number that would be expressed, I guess, in square dates.)
For number 2, application programmers might introduce subtle errors by not allowing room for the sign in UI code, which might make -123456 look like 123456.
The third has to do with writing code that returns id numbers. Code that returns a single id number might return -1 as an error code. But -1 is a valid ID number in most cases. (Most databases don't restrict id numbers to the range of non-negative integers.)
Hibernate and Postgresql negative primary key id issue
change to
@SequenceGenerator(name = "product_seq_gen", sequenceName = "product_idproduct_seq", initialValue = 1, allocationSize = 1)
EF Core with Postgres is generating negative primary keys
I found something that I changed in the code that caused this behavior. In my base repository when adding a new entity I changed DbContext.Set<T>().Add(entity)
to DbContext.Attach(entity).State = EntityState.Added;
The reason I did this was because when using attach EF will automatically detect existing related entities. Using only add would try and add related entities again whether they already exist in the db or not.
I have no idea why it would suddenly switch to a negative int when generating the ID.
Related Topics
How to Create a User in SQL Server Express Database I Added to My Project
Combining Results of Two Select Statements
Conditional Where Clause with Case Statement in Oracle
Difference Between N'String' VS U'String' Literals in Oracle
JSONb Query with Nested Objects in an Array
Avoiding Concurrency Problems with Max+1 Integer in SQL Server 2008... Making Own Identity Value
Alter Table Then Update in Single Statement
Why Select Top Clause Could Lead to Long Time Cost
Sum Columns with Null Values in Oracle
Omitting the Milliseconds in a Date
Sql: How to Use Union and Order by a Specific Select
How to Edit Values of an Insert in a Trigger on SQL Server
How to Create an Alias of Database in SQL Server
Query to Find Nᵗʰ Max Value of a Column
MySQL Select Query String Matching
Generate_Series in Postgres from Start and End Date in a Table
How to Check If Identity_Insert Is Set to on or Off in SQL Server