title: How do I create a table with self-referencing fields in MySQL?
body: <p>In GTFS, the field <code>parent_station</code> is either from <code>stop_id</code> (self-reference) or <code>NULL</code>. For instance (note that <code>parent_station</code> might be <code>NULL</code>), </p>
  
  <pre><code>SELECT stop_id, stop_name, parent_station FROM stops where stop_id='1970324837184617' OR parent_station='1970324837184617';
  
  +------------------+----------------------+------------------+
  | stop_id          | stop_name            | parent_station   |
  +------------------+----------------------+------------------+
  | 1970324837184617 | Saint Agne-SNCF      |                  |
  | 3377699720880648 | Saint Agne-SNCF      | 1970324837184617 |
  | 3377699720880649 | Saint Agne-SNCF      | 1970324837184617 |
  | 3377699722011100 | Saint Agne-SNCF      | 1970324837184617 |
  | 3377699722011101 | Saint Agne-SNCF      | 1970324837184617 |
  | 3377699722914835 | Saint Agne Gare SNCF | 1970324837184617 |
  +------------------+----------------------+------------------+
  6 rows in set (0,01 sec)
  </code></pre>
  
  <p>I create a self-referencing table with:</p>
  
  <pre><code>CREATE TABLE `stops` (
      stop_id VARCHAR(255) NOT NULL PRIMARY KEY,
      stop_code VARCHAR(255),
      stop_name VARCHAR(255),
      stop_desc VARCHAR(255),
      stop_lat DECIMAL(8,6),
      stop_lon DECIMAL(8,6),
      location_type INT(2),
      parent_station VARCHAR(255),
      -- the field `parent_station` might be blank (NULL)
      FOREIGN KEY parent_station REFERENCES stops(stop_id)  -- self-referential table fields
  );
  </code></pre>
  
  <p>but encounter the issue,</p>
  
  <blockquote>
    <p>ERROR 1064 (42000) at line 99: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY parent_station REFERENCES stops(stop_id)<br>
    )' at line 11</p>
  </blockquote>
  
  <hr>
  
  <p>With the help of @Awita, I correct the syntax and the table is created successfully.</p>
  
  <pre><code>FOREIGN KEY (parent_station) REFERENCES stops(stop_id) 
  </code></pre>
  
  <p>But a new issue occurs while loading local data.</p>
  
  <pre><code>LOAD DATA LOCAL INFILE 'stops.txt' INTO TABLE stops FIELDS TERMINATED BY ',' IGNORE 1 LINES;
  </code></pre>
  
  <p>The error:</p>
  
  <pre><code>ERROR 1452 (23000) at line 140: Cannot add or update a child row: a foreign key constraint fails (`paris_gtfs`.`stops`, CONSTRAINT `stops_ibfk_1` FOREIGN KEY (`parent_station`) REFERENCES `stops` (`stop_id`))
  </code></pre>
  
  <p>This issue is caused by <strong>NULL</strong> <code>parent_station</code>?</p>