title: parent_id a foreign key(self reference) and null?
body: <p>Going through Bill Karwin book “SQL Antipatterns”, chapter 3, Naive Trees (adjacency table, parent-child relationship) there is an example for a comment table.</p>
  
  <pre><code>CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  parent_id BIGINT UNSIGNED,
  comment TEXT NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
  );
  </code></pre>
  
  <p>Sample data</p>
  
  <pre><code>| comment_id | parent_id | comments
  |------------| ----------|-------------------------------------
  |1           | NULL      |What’s the cause of this bug?
  |2           | 1         |I think it's a null pointer
  |3           | 2         |No, I checked for that
  |4           | 1         |We need to check for invalid input
  |5           | 4         |Yes,that's a bug
  |6           | 4         |Yes, please add a check
  |7           | 6         |That fixed it
  </code></pre>
  
  <p>The table has a comment_id, parent_id and a comment column. The parent_id is a foreign key referring to the comment_id.</p>
  
  <p>The comment_id auto increment starting from 1.</p>
  
  <p>Question.</p>
  
  <p>If parent_id is supposed to be a foreign key which refers to the comment_id then how come the row with the comment_id = 1 have parent_id null/0 when the purpose of having a foreign key is to ensure referential integrity.</p>
  
  <p>Note: I created the table as it is and tried entering the data and got this error </p>
  
  <blockquote>
    <p>#1452 - Cannot add or update a child row: a foreign key constraint fails (`category`.`comments`, CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`comment_id`))</p>
  </blockquote>