is it invalid to set a reference to null or 0?

Question

I'll be doing this in sqlite now that they support foreign keys and tsql and perhaps mysql.

Is it illegal to do something like

CREATE TABLE comment(
id integer primary key,
parent integer references(comment.id),
author integer references(User.id),
desc varchar(max),
hidden  bit
deleted bit
);

where parent may be 0 or null because it is the root and does not have a parent? AFAIK i wont be deleting any comments but will set it to hidden or as deleted. I mostly want to know if i can have the root as 0 or null otherwise i have no idea how i can have the comment have a reference a parent (i cant make the first one point to itself?).

Answer 1

a column with a FOREIGN KEY can be nullable, but it can't hold a '0' if there's no corresponding row in the parent table with a '0' in it. '0' is a value whereas NULL represents "no value".

So in this case the root nodes of the "adjacency list" structure you're setting up will have NULL for the value of "parent".