0

I use lookup tables commonly and sometimes I have some default record in the Lookup table and I think for this record I can use 0 as Primary Key a as shown below:

Project:

Id   |  Name        | Description
----------------------------------------------------
0    |  General     | Used for general situations
1    |  Project A   | ...
2    |  Project B   | ...
3    |  Project C   | ...

By doing this I can start from 1 for the actual project values (A, B, C...) and on the other hand I can easily distinguish general/default values for the other tables as FK.

Status:

Id   |  ProjectId   | Name        
----------------------------------------------------
1    |  0           | Deleted     
2    |  0           | Active
3    |  0           | Passive
4    |  1           | Waiting for Approval
5    |  2           | Waiting for Sales
6    |  3           | Waiting for Service

Is the approach above wrong or will there be a problem by using 0 as PK/FK?

  • 1
    Primary key is a database feature. Java or C# don't have the concept of primary (or foreign) keys. The languages won't care even if you use `NULL` for the primary key, if the [database allows it](https://stackoverflow.com/questions/3906811/null-permitted-in-primary-key-why-and-in-which-dbms). – Kayaman Jun 03 '20 at 11:46
  • it makes no sense to link your primary key to your data. – Stultuske Jun 03 '20 at 11:47
  • Thanks, actually I think of Entity Framework Code First approach and for this reason I thought Java/C#. I updated tags. –  Jun 03 '20 at 11:49
  • Since it's a surrogate key, you shouldn't really care whether it's `0`, `1`, `-42` or `4243`. If you're going to write logic based on the value of the primary key, you're off to bad design. – Kayaman Jun 03 '20 at 11:53
  • The value of Primary Key is *normally* completely arbitrary. Often it's not even exposed to the presentation layer, as it's purely used to provide relationship between objects. The value to is displayed is often the "name" related to that ID, in this case the Project's Name. A PK doesn't have to start at `1`. In fact, in some designs you'll find that people start at -2,147,483,648 (or even -9,223,372,036,854,775,808) to get the greatest number of possible values from the `int`(`bigint`) datatypes alongside `IDENTITY`. – Thom A Jun 03 '20 at 11:55
  • Either way, at best, this is purely down to opinion, and thus off topic for SO. It's up to you what starting value, increment, etc, you use in your PK. – Thom A Jun 03 '20 at 11:55

1 Answers1

2

0 is just another valid number for any numeric data type. It can be written to PK or FK columns like any other. Positively tested in Postgres, but most certainly in any relational RDMS.

serial or IDENTITY columns typically start at 1, but that's just a convention.

But note that the lower bound of a SEQUENCE is 1 by default in Postgres (unless customized). So while you can set the value of these columns to 0, you cannot reset their underlying sequence to 0 with setval() (unless customized). See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I just learned the hard way that Telerik kendo grids do poorly when the data source has an int PK field and a record is present in the data with 0 as the PK value. This may be an obscure edge case, but it has me rethinking life decisions. – Wellspring Aug 26 '22 at 15:01