Stupid question here. Let's say you a simple setup where users submit articles and an article only belongs to one category. Categories are N level deep using the parent_id concept.
The categories table:
Here you definitely want the parent_id to default to NULL to indidcate that by default all new categories belong to root until otherwise assigned. And of course we can get all the top level categories with:
create table categories (
cat_id SERIAL PRIMARY KEY,
parent_id default NULL,
CREATE INDEX parent_id_idx ON categories(parent_id);
SELECT * FROM categories WHERE parent_id IS NULL
Now with the articles table it's possible a user can create an article but not assign it a category right away because he/she is not really sure which category it falls under.
Would you use default NULL here for the cat_id FK or instead have it default to 1 and create a category with that id for unassigned articles? Default 0 obviously won't work because that category doesn't exist and with referential integrity it won't insert the article at all. The other benefit of assigning it default to NULL is we can easily search for all articles that have not been assigned a category yet.
create table articles (
article_id SERIAL PRIMARY KEY
user_id INTEGER NOT NULL,
cat_id INTEGER default NULL,
CONSTRAINT cat_id_exists FOREIGN KEY(cat_id) REFERENCES categories(cat_id)
So which would you do here? default NULL seems the reasonable answer but I just want to make sure.