title: Preventing circular joining, recursive searches
body: <p>So in my situation I have three tables: <code>list</code>, <code>item</code> and <code>list_relation</code>.</p>
  
  <p>Each <code>item</code> will be linked to a list through the <code>list_id</code> foreign key.</p>
  
  <p>the <code>list_relation</code> looks like this:</p>
  
  <pre><code>CREATE TABLE list_relation
      (
          parent_id INT UNSIGNED NOT NULL,
          child_id INT UNSIGNED NOT NULL,
  
          UNIQUE(parent_id, child_id)
  
          FOREIGN KEY (parent_id)
              REFERENCES list (id)
                  ON DELETE CASCADE,    
  
          FOREIGN KEY (child_id)
              REFERENCES list (id)
                  ON DELETE CASCADE
      );
  </code></pre>
  
  <p>I want to be be able to inherit from multiple lists as well (which includes the related items).</p>
  
  <p>For example I have list: 1, 2, 3.</p>
  
  <p>I was wondering if there was any SQL way to prevent there from being a circular relation. E.g.</p>
  
  <p>List 1 inherits from List 3, List 2 inherits from List 1, List 3 inherits from List 1.</p>
  
  <p><code>1 -&gt; 2 -&gt; 3 -&gt; 1</code></p>
  
  <p>My current idea is that I would have to find out whether it would be circular by validating the desired inheritance first then inserting it into the DB.</p>