SQLite 게시물 업데이트 내역 저장

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE post_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  post_id INTEGER NOT NULL,
  title TEXT,
  content TEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE
);

-- 업데이트 전 내역 저장
INSERT INTO post_history (post_id, title, content, updated_at)
SELECT id, title, content, updated_at FROM posts WHERE id = 1;

-- 게시물 업데이트
UPDATE posts SET title = '새 제목', updated_at = CURRENT_TIMESTAMP WHERE id = 1;

posts : post_history = 1 : N 관계

#sqlite#database-design
#332