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?
Advertisement
Answer
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: