title: MySQL: what's the difference between INDEX, UNIQUE, FOREIGN KEY, and PRIMARY KEY?
body: <p>Ok, so i'm a newbie here at SQL..</p>
  
  <p>I'm settings up my tables, and i'm getting confused on indexes, keys, foreign keys..</p>
  
  <p>I have a users table, and a projects table.</p>
  
  <p>I want to use the users (id) to attach a project to a user.</p>
  
  <p>This is what I have so far:</p>
  
  <pre class="lang-sql prettyprint-override"><code>DROP TABLE IF EXISTS projects;
  
  CREATE TABLE projects (
      id int(8) unsigned NOT NULL,
      user_id int(8),
      name varchar(120) NOT NULL,
      description varchar(300),
      created_at date,
      updated_at date,
      PRIMARY KEY (id),
      KEY users_id (user_id)
  ) ENGINE=InnoDB;
  
  ALTER TABLE projects (
      ADD CONSTRAINT user_projects,
      FOREIGN KEY (user_id) REFERENCES users(id),
      ON DELETE CASCADE
  )
  </code></pre>
  
  <p>So what I'm getting lost on is <strong>what is the differences between</strong> a <strong>key</strong>, an <strong>index</strong>, a <strong>constraint</strong> and a <strong>foreign key?</strong></p>
  
  <p>I've been looking online and can't find a newbie explanation for it.</p>
  
  <p>PS. I'm using phpactiverecord and have the relationships set up in the models</p>
  
  <p>user-> has_many('projects');</p>
  
  <p>projects -> belongs_to('user');</p>
  
  <p>Not sure if that has anything to do with it, but thought i'd throw it in there.. </p>
  
  <p>Thanks.</p>
  
  <p>EDIT:</p>
  
  <p>I thought it could possible be something to do with Navicat, so I went into WampServer -> phpMyAdmin and ran this... </p>
  
  <pre class="lang-sql prettyprint-override"><code>DROP TABLE IF EXISTS projects;
  
  CREATE TABLE projects (
      id int(8) unsigned NOT NULL,
      user_id int(8) NOT NULL,
      name varchar(120) NOT NULL,
      description varchar(300),
      created_at date,
      updated_at date,
      PRIMARY KEY (id),
      KEY users_id (user_id),
      FOREIGN KEY (user_id) REFERENCES users(id)
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p>Still nothing... :(</p>