gistfile1.sql

create table users (
  id        serial    primary key,
  username  text      not null,
  password  text      not null,
  created   timestamp
);
  
INSERT INTO users (username, password, created) VALUES('osaka', '', NOW());
INSERT INTO users (username, password, created) VALUES('kyoto', '', NOW());
INSERT INTO users (username, password, created) VALUES('hyogo', '', NOW());
INSERT INTO users (username, password, created) VALUES('nara', '', NOW());
INSERT INTO users (username, password, created) VALUES('wakayama', '', NOW());
INSERT INTO users (username, password, created) VALUES('mie', '', NOW());
      
      
create table posts (
  id        serial    primary key,
  title     text      not null,
  body      text      not null,
  created   timestamp,
  modified  timestamp,
  user_id   int       not null,
    
  foreign key(user_id) references users(id) on update cascade on delete cascade
);    
      
      
INSERT INTO posts (title, body, created, user_id) VALUES ('大阪', 'これは、記事の本文でっせー。', NOW(), 1);INSERT INTO posts (title, body, created, user_id) VALUES ('また大阪か', 'そこに本文が続きまんがな。', NOW(), 1);
INSERT INTO posts (title, body, created, user_id) VALUES ('大阪の逆襲', 'こりゃほんまおもろいで！って、うそやねん〜', NO
W(), 1);
      
create table tags ( 
  id        serial    primary key,
  name      text      not null
);
  
INSERT INTO tags (name) VALUES('CakePHP');
INSERT INTO tags (name) VALUES('関西');
INSERT INTO tags (name) VALUES('これはひどい');
INSERT INTO tags (name) VALUES('あとで読む');

create table post_tags (
  id        serial    primary key,
  post_id   int       not null,
  tag_id    int       not null,
  foreign key(post_id) references posts(id) on update cascade on delete cascade,
  foreign key(tag_id) references tags(id) on update cascade on delete cascade
);