Negative Primary Keys

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.

  1. It violates the principle of least surprise.
  2. Some people assume all ID numbers are non-negative.
  3. 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



Leave a reply



Submit