title: sqlite what happens to id when you delete a row with a foreign key?
body: <p>I have these two tables:</p>
  <pre><code>class User(Base):
      &quot;&quot;&quot; User entry in database &quot;&quot;&quot;
      __tablename__ = &quot;users&quot;
      # ID uniquely identifies 
      id             = Column(Integer, primary_key=True, index=True)
      kerberos       = Column(String, unique=True, index=True)
      hashpass       = Column(String)
      is_active      = Column(Boolean, default=True)
      creation_date  = Column(DateTime, default=None)
  
      scores = relationship(&quot;Score&quot;, back_populates=&quot;owner&quot;)
  
  class Score(Base):
      &quot;&quot;&quot; Stores each users scores &quot;&quot;&quot;
      __tablename__ = &quot;scores&quot;
      
      id    = Column(Integer, primary_key=True, index=True)
      user_id = Column(String, ForeignKey(&quot;users.id&quot;), index=True)    
      score = Column(Integer)
      time = Column(DateTime)
      
      owner = relationship(&quot;User&quot;, back_populates=&quot;scores&quot;)
  </code></pre>
  <p>I have read that adding new &quot;Users&quot; will auto-increment the &quot;id&quot; field.
  Now each entry in the &quot;scores&quot; table has a foreign key pointing to users.id. This means (correct me if I'm wrong), that each &quot;score&quot; entry maps to one and only unique User (so the score:user relationship is many:one).</p>
  <p>I'm wondering if this is a good design, especially if I delete a user, what happens?
  Does deleting a user automatically invalidate/delete the corresponding scores that are linked thru the deleted user's id (foreign key for those scores)?  I am concerned that deleting a user will all user id's afterwards to shift down by one, so there would be an off by one issue when looking at a user's scores.</p>
  <p>Is this good design? Feel free to link resources/redirect to another question (I couldn't not find any specifically)</p>