【kSQL 実践 #4】データ品質を監査する — VALIDATE で制約違反を洗い出し、重複と文字列の罠を押さえる

Posted at 2026-09-16

結論（3 行）

kintone はフォームの制約を変えても既存レコードを再点検しません。VALIDATE APPn は、実行主体が取得できる保存済みレコードを、現在取得できるフォーム制約と任意の CHECK に照らし、違反を行で返す read-only 文です。INTO #err と ASSERT を組めば「違反 0 件」をゲートにできます

重複候補・表記ゆれ候補は GROUP BY … HAVING COUNT(*) > 1 の定石がそのまま使えます。正規化した値でグループ化すれば「株式会社」の前後違いも拾えます

文字列には kintone 固有の罠が 3 つあります。並び順はコードポイント順、文字数は UTF-16 単位、LIKE は JavaScript で大文字小文字を区別し押し下がらない、KLIKE は kintone REST クエリの like で区別せず押し下がる

前回（第 3 回: ABC 分析）までは集計でした。今回は集計の前提になる「データが正しいか」を SQL で確かめます。
kintone のデータ品質の問題は、たいていエラーにならずに溜まります。フォームの選択肢を変えた後の古い値、下書きで空のまま保存された必須項目、同じ会社の表記ゆれ。集計してから気づくと、原因の行を探すところから始まります。

課題

営業支援パックの顧客管理（APP4148）と案件管理（APP4149）について、次を定期的に確かめたい。

既存レコードが今のフォーム制約（必須・文字数・選択肢）を満たしているか
業務ルール（受注なのに受注予定日が空、売上が未入力）に反する案件が無いか
電話番号の重複、会社名の表記ゆれが無いか

1. `VALIDATE` — 既存レコードをフォーム制約に照らす

標準 SQL に相当する文はありません。kintone の REST API にも、保存済みレコードをフォーム定義で再検証する機能はありません。kSQL の VALIDATE は、フォーム定義とレコードを読み取り、kSQL の中で制約を検査します。書き込み API は呼びません。

VALIDATE APP4148

以下の表では、固定 9 列のうち説明に使う列だけを抜粋しています。

$id	$err_field	$err_code	$err_message	$err_value
7	業種	ERR_CHOICE_INVALID	業種に定義外の選択肢があります	金融業
9	業種	ERR_CHOICE_INVALID	業種に定義外の選択肢があります	金融業

手元の顧客管理では、レコード 7 と 9 の「業種」に、ドロップダウンの選択肢に無い値「金融業」が入っていました。これは SFA パックの初期データそのものなので、あなたの環境でも同じ 2 件が出るかもしれません（パックの版によって違う可能性はあります）。
一覧で見ても普通に「金融業」と表示されるので、フォームを開いて編集しようとするまで気づけない種類の問題です。

結果は固定 9 列で、$err_count には同一レコード・同一違反の本数が入ります。監査で標準的に検査されるのは、必須、文字数の最小・最大、数値の範囲と整数部桁数、選択系の選択肢、サブテーブル内の子フィールドの同じ制約です。
結果のメタデータ validateStats には、違反レコード数と集約前の違反総数が付きます。さらに constraintMetadata には、実際の監査対象フィールドについて、取得したメタデータに含まれていた既知 4 種のフォーム制約（必須・文字数・数値範囲・選択肢）が present / absent で示されます。
これは観測できたメタデータの内訳であり、「アプリに制約が存在しない」ことの証明ではありません。CHECK と NUMBER の型・精度検証もこの 4 種には含まれません。「違反 0 件」を読むときは、この内訳と対象フィールド、実行主体の権限を併せて見ます。

`CHECK` で業務ルールを足す

フォーム制約に無いルールは CHECK WHEN … THEN 'メッセージ' で足します。上から順に評価され、最初に当たった WHEN のメッセージが返ります。

VALIDATE APP4149
CHECK WHEN 商談フェーズ = '受注' AND 受注予定日 = '' THEN '受注なのに受注予定日が空'
      WHEN 売上 = '' THEN '売上が未入力'
      WHEN 顧客No_ = '' THEN '顧客が未設定'

$id	$err_code	$err_message
338	ERR_CHECK	売上が未入力

手元では検証用に足した 1 件だけが引っかかりました。CHECK の条件はローカルで評価されるので、第 1 回で IN に揃えた選択系の条件も = のままで問題ありません。第 1 回で「結合キーが空だと JOIN 先が全件取得に戻る」と書いた 顧客No_ = '' も、ここで先回りして検出できます。

1 つの CHECK ブロックは先勝ちで、1 レコードにつき最初に当たった WHEN の 1 件しか出ません。上の 3 条件のように互いに無関係な検査を全部出したいなら、CHECK ブロックを 3 つ並べます。ブロック同士は独立に評価されます。

VALIDATE APP4149
CHECK WHEN 商談フェーズ = '受注' AND 受注予定日 = '' THEN '受注なのに受注予定日が空'
CHECK WHEN 売上 = '' THEN '売上が未入力'
CHECK WHEN 顧客No_ = '' THEN '顧客が未設定'

`INTO #err` と `ASSERT` でゲートにする

バッチにすると、違反を一時テーブルに受けて、件数で止められます。

VALIDATE APP4149 CHECK WHEN 売上 = '' THEN '売上が未入力' INTO #err;
ASSERT (SELECT COUNT(*) FROM #err) = 0;
SELECT $id, $err_message FROM #err

違反があると 2 文目で止まり、3 文目は実行されません。

AssertError: assertion failed: (SELECT COUNT(*) FROM #err) = 0 (actual: 1).

プラグインの実行画面でもこのバッチはそのまま動きます。「違反 0 件なら黙って通り、あれば止まって件数を見せる」という形は、第 5 回の一括更新の前段（更新前に品質を確かめる）にそのまま使います。CLI から定期実行して終了コードで検知する形は第 7 回で扱います。

内訳を数えるときは、行数の COUNT(*) ではなく SUM($err_count) を使います。サブテーブルの同一違反は 1 行にまとまり、本数が $err_count に入るためです。

VALIDATE APP4148 INTO #err;
SELECT $err_field, $err_code, SUM($err_count) AS 違反数
FROM #err
GROUP BY $err_field, $err_code
ORDER BY 違反数 DESC

仕組みと制約

read-only。フォーム定義とレコードを GET するだけで、POST / PUT / DELETE は呼びません。EXPLAIN VALIDATE はフォーム定義と、NUMBER が対象に含まれる場合は数値精度設定だけを読みます。レコード API と書き込み API は呼ばず、違反件数も算出しません
監査範囲は実行主体が取得できるレコードだけです。プラグインではログイン中のユーザー、CLI / MCP では設定した認証主体の閲覧権限が効きます。定期監査で最も危険なのは、権限で見えないレコードを「違反なし」と読んでしまうことです
完全な監査集合が要るので、取得上限に達したら打ち切りではなくエラーです（onLimit=truncate は無効化されます）。上限は API から取得する候補件数以上にします。WHERE に LIKE や IS NULL のような押し下がらない条件を書くと、違反候補が少なくても全件取得になります
WHERE で対象を絞れます（VALIDATE APP4148 WHERE 作成日時 >= '2026-01-01'）。通常比較・BETWEEN・リテラル IN・IS NULL・LIKE が使え、KLIKE とサブクエリは使えません
大量違反は SUMMARY で、親×サブテーブル×フィールド×コード単位に集約できます（固定 5 列。トップレベルの違反では $err_subtable が空）
対象フィールドを VALIDATE APP4148 (会社名, 業種) のように絞れます。省略時は制約を持つ全フィールドと全 NUMBER

2. 重複候補と表記ゆれ候補 — `GROUP BY … HAVING`

ここは標準 SQL のままです。ただし SQL が返すのは「候補」で、同じ電話番号の別会社や、同名の別法人はあり得ます。この結果から自動で更新はしません。判断は人が行い、書き戻しは第 5 回の形で行います。

電話番号の重複候補

SELECT 電話番号, COUNT(*) AS 件数, MIN(会社名) AS 例
FROM APP4148
WHERE 電話番号 != ''
GROUP BY 電話番号
HAVING COUNT(*) > 1
ORDER BY 件数 DESC, 電話番号

電話番号	件数	例
03-xxxx-xxxx	8	サイボウズ物産株式会社
045-xxxx-xxxx	2	株式会社橘川ケミカル

サンプルデータの電話番号はプレースホルダなので、これは「ダミー値が本番に混ざっていないか」の検査として読めます。
WHERE 電話番号 != '' を付けているのは、空を 1 つのグループとして数えないためです（第 2 回の「空の日付は '' のグループになる」と同じ）。この条件は押し下がります。

会社名の表記ゆれ候補

「株式会社」が前に付くか後ろに付くかの違いは、GROUP BY 会社名 では別の会社です。正規化した値でグループ化します。
正規化は CTE で列にしてから集計します。先頭または末尾の「株式会社」だけを外し、半角と全角の空白を除きます。

WITH 正規化済み AS (
  SELECT 会社名,
         REGEXP_REPLACE(
           REGEXP_REPLACE(TRIM(会社名), '^(株式会社)|(株式会社)$', ''),
           '[ 　]', ''
         ) AS 正規化名
  FROM APP4148
)
SELECT 正規化名, COUNT(*) AS 件数, GROUP_CONCAT(会社名) AS 元の表記
FROM 正規化済み
GROUP BY 正規化名
HAVING COUNT(*) > 1
ORDER BY 件数 DESC

正規化名	件数	元の表記
サイボウズ物産	2	株式会社サイボウズ物産,サイボウズ物産株式会社

GROUP_CONCAT で元の表記を並べておくと、どちらに寄せるかをその場で判断できます。

正規化の強さは用途で選びます。REPLACE(会社名, '株式会社', '') のように場所を問わず消すと候補は広く拾えますが、東京株式会社サイボウズ のような中間の文字列まで消して、別の会社を同じ候補にまとめます（手元ではこの緩い式だと「東京サイボウズ」の 2 件がもう 1 組出ます）。監査候補を広く見たいのか、誤検出を減らしたいのかで決め、どちらにせよ結果は人が確認します。
使える関数は REPLACE / TRIM / UPPER / LOWER、1 文字ずつ写す TRANSLATE、正規表現の REGEXP_REPLACE です。

なお、この節と次節に出る「サイボウズ物産」「東京サイボウズ」系の 5 社は、表記ゆれの検証用に手で足したレコードです。原本 9 件と第 6 回の増量 200 件には無いので、同じ結果を見るなら顧客管理に足してください。

見つけた表記ゆれを書き戻すのは第 5 回（UPDATE … FROM で「変わる行だけ」を更新する形）で扱います。

3. 文字列の罠 — SQL 経験者ほど踏む 3 つ

並び順は Unicode コードポイント順

SELECT 会社名 FROM APP4148 WHERE 会社名 LIKE '%サイボウズ%' ORDER BY 会社名

会社名
サイボウズ物産株式会社
東京株式会社サイボウズ
株式会社サイボウズ商事
株式会社サイボウズ東京
株式会社サイボウズ物産
株式会社東京サイボウズ

カタカナ（サ U+30B5）→ 東（U+6771）→ 株（U+682A）の順です。「五十音順」でも「読み順」でもなく、文字コードの順です。
MySQL の utf8mb4_general_ci や PostgreSQL の ja_JP ロケールの感覚で「並んでいるはず」と思うと違います。kintone 自体の一覧の並び順もコードポイント順なので（実測）、kSQL の ORDER BY はそれに合わせています（a と A も別で、A が先）。
読み順で並べたいなら、フリガナのフィールドを持ってそれで並べます。SQL 側で照合順序を指定する機能はありません。

文字数は UTF-16 単位、`LENGTH_CHAR` はコードポイント

SELECT LENGTH('𠮷野家') AS length_値, LENGTH_CHAR('𠮷野家') AS length_char_値

length_値	length_char_値
4	3

kintone の「文字数」制限は UTF-16 コードユニットで数えるので、𠮷（サロゲートペア）は 2 文字分です。上限チェックは LENGTH、人が数える文字数に近いのは LENGTH_CHAR です。差がサロゲートペアの個数なので、外部連携の前に洗い出せます。

SELECT $id, 会社名, LENGTH(会社名) - LENGTH_CHAR(会社名) AS ペア数
FROM APP4148
WHERE LENGTH(会社名) - LENGTH_CHAR(会社名) > 0

é のような「UTF-8 では 2 バイトだが 1 コードユニット」の文字は差 0 なので誤検知しません。Shift_JIS で出せない漢字を変換する TRANSLATE の話は第 7 回（CSV 出力）で扱います。

`LIKE` と `KLIKE` は別物

	`LIKE`	`KLIKE`
評価する場所	JavaScript（インメモリ）	kintone REST クエリの `like`
押し下げ	しない。単独なら `fetch: ALL`	押し下げる。WHERE 全体を渡せれば `EXACT`、残余条件があれば `PREFILTERED`（実測: `KLIKE … AND LENGTH(会社名) > 10` で `PREFILTERED`）
一致規則	`%` / `_` を使う文字列一致。大文字小文字を区別する	大小文字・全半角・語分割など、kintone のキーワード検索の規則。大文字小文字は区別しない
上限	取得上限	10 万件で検索が打ち切られる（kSQL は打ち切りを検出してエラーにする）

手元で 会社名 LIKE '%サイボウズ%' と 会社名 KLIKE 'サイボウズ' はどちらも 6 件で、結果は同じでした。違いが出るのは大文字小文字です。

SELECT 案件名 FROM APP4149 WHERE 案件名 LIKE '%KINTONE%'          -- 0 件
SELECT 案件名 FROM APP4149 WHERE LOWER(案件名) LIKE '%kintone%'   -- 1 件（kintone SFA提案）
SELECT 案件名 FROM APP4149 WHERE 案件名 KLIKE 'KINTONE'           -- 1 件（kintone SFA提案）

LIKE で大文字小文字を無視したいなら LOWER() で揃えます。KLIKE は kintone の検索なので最初から無視します。
どちらを使うかは「何と同じ結果が欲しいか」で決めます。 kintone REST クエリの like と同じ検索規則が欲しいなら KLIKE、文字列に対して % / _ のパターンを厳密に評価したいなら LIKE（または REGEXP_LIKE）です。大きいアプリで LIKE を使うと全件取得なので、押し下がる条件を AND で足して PREFILTERED にします（第 0 回）。

落とし穴のまとめ

VALIDATE は完全入力が必要。 上限到達はエラー。対象を WHERE で絞るか上限を上げる
VALIDATE の内訳は SUM($err_count)。 COUNT(*) は行数
「違反 0 件」だけで監査範囲まで保証されたとは考えない。 constraintMetadata は取得できたメタデータの内訳なので、対象フィールドと実行主体の権限を併せて確認する
空値は 1 つのグループになる。 重複検査では WHERE 列 != '' で除く
正規化値はいったん CTE の列にする。 同じ SELECT の別名を GROUP BY で使うと、同名の物理フィールドがある場合はそちらが優先される
並び順はコードポイント順。 読み順が要るならフリガナ列
文字数は UTF-16 単位。 上限は LENGTH、人の感覚は LENGTH_CHAR
LIKE は大文字小文字を区別し、押し下がらない。 KLIKE は区別せず押し下がるが、一致規則は kintone 準拠で 10 万件打ち切りがある
書き込み直後の確認に KLIKE を使わない。 検索索引は即時反映とは限らず、更新前の値がヒットしたり、更新後の値が見えなかったりする。確認は索引反映を待って別実行にするか、取得量を確認したうえで LIKE を使う。待つ時間は固定できないので、直後確認をゲートに使うなら LIKE 側（第 5 回）
CHECK の条件はトップレベルのフィールドだけ。 サブテーブル子や修飾参照は書けない

運用に載せる

プラグインの専用アプリには、用途別に 2 本を SQL レコードとして保存します。

手動監査用: VALIDATE … INTO #err; SELECT … FROM #err で違反の内訳を表示する。毎週これを履歴から実行する
一括更新の前段用: VALIDATE … INTO #err; ASSERT (SELECT COUNT(*) FROM #err) = 0; で違反があれば処理を止める。ASSERT で止まると後続は実行されないので、内訳を見る目的には向かない

「違反があったら止める」を自動化する（終了コードで検知して通知する）のは CLI の役割で、第 7 回で扱います。

次回は第 5 回「安全に一括更新する」です。今回見つけた表記ゆれや未入力を、VALIDATE ONLY と ASSERT で守りながら書き戻します。

リポジトリ・ドキュメント:

https://github.com/rex0220/kintone-sql-tools
npm: @rex0220/kintone-sql-tools（CLI / プラグイン / MCP サーバー同梱）

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up