-- 親のないblogを探す

CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS
  SELECT blog.id,val FROM blog JOIN blog_s bs
            ON blog.id=bs.id AND key='owner'
            WHERE val NOT IN (SELECT gname FROM grp)
              AND val NOT IN (SELECT name FROM user);

SELECT * FROM orphanedblog;

SELECT id FROM orphanedblog;

-- 削除処理
SELECT count(*) FROM article;
DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);
SELECT count(*) FROM article;

-- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!!

CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS
  SELECT id FROM article
  WHERE  blogid NOT IN (SELECT id FROM blog);

SELECT count(*) FROM orphanedarticle;

SELECT id FROM orphanedarticle;

-- 削除処理
SELECT count(*) FROM article;
DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
SELECT count(*) FROM article;