title: Have two SQLite databases reference eachother
body: <p>I'm very new to SQL. I've read through a bunch of questions on how to join or attach two different SQLite databases together but I haven't founds success using any of the methods described. The problem is likely rooted in my own misunderstanding of how SQL works but here goes:</p>
  
  <p>I'd like to create two different SQL databases, one for users of a site, and one for messages sent between users.</p>
  
  <p>The schema for the <code>users</code> db (filename: blog_users.sqlite) looks like this:</p>
  
  <pre><code>DROP TABLE IF EXISTS user;
  
  CREATE TABLE user (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   username TEXT UNIQUE NOT NULL,
   password TEXT NOT NULL
  );
  </code></pre>
  
  <p>The schema I came up with for the <code>messages</code> db looks like this:</p>
  
  <pre><code>DROP TABLE IF EXISTS user;
  DROP TABLE IF EXISTS message;
  
  ATTACH DATABASE 'blog_users.sqlite' AS 'users';
  
  CREATE TABLE user (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   username TEXT UNIQUE NOT NULL
  );
  
  CREATE TABLE message (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   author_id INTEGER NOT NULL,
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   title TEXT NOT NULL,
   body TEXT NOT NULL,
   FOREIGN KEY (author_id) REFERENCES users.user (id)
  );
  </code></pre>
  
  <p>The ability to create a message should be constrained by requiring a user id which should be pulled from the <code>users</code> db, which is why I tried to use <code>FOREIGN KEY</code>. </p>
  
  <p>Can someone explain what I'm doing wrong, or point me in the right direction?</p>
  
  <p>Thanks in advance</p>