CREATE TABLE post (
  id bigint(20) unsigned NOT NULL auto_increment,
  user_id bigint(20) unsigned NOT NULL,
  title char(255) NOT NULL,
  contents text NOT NULL,
  PRIMARY KEY  (id),
  KEY user_id (user_id)
) TYPE=InnoDB;

CREATE TABLE reaction (
  id bigint(20) unsigned NOT NULL auto_increment,
  user_id bigint(20) unsigned NOT NULL,
  post_id bigint(20) unsigned NOT NULL,
  content text NOT NULL,
  PRIMARY KEY  (id),
  KEY user_id (user_id),
  KEY post_id (post_id)
) TYPE=InnoDB;


CREATE TABLE `user` (
  id bigint(20) unsigned NOT NULL auto_increment,
  username char(20) NOT NULL,
  `password` char(32) NOT NULL,
  first_name char(50) NOT NULL,
  last_name char(50) NOT NULL,
  join_date int(11) NOT NULL,
  email_address char(100) NOT NULL,
  activated enum('y','n') NOT NULL default 'n',
  role enum('member','admin') NOT NULL default 'member',
  description text NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY username (username)
) TYPE=InnoDB;

ALTER TABLE `post`
  ADD CONSTRAINT post_ibfk_1 FOREIGN KEY (user_id) REFERENCES `user` (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `reaction`
  ADD CONSTRAINT reaction_ibfk_2 FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT reaction_ibfk_1 FOREIGN KEY (user_id) REFERENCES `user` (id) ON DELETE CASCADE ON UPDATE CASCADE;