質問とシナリオ
レガシーな orders テーブルに、親となる顧客が存在しない customer_id の値が少数含まれています。チームは、過去データのワンショット修復をブロックすることなく、ターゲットスキーマを明示するために PostgreSQL 18 でリレーションを宣言したいと考えています。NOT ENFORCED が適切かどうかを判断し、通常の外部キーとの違いを説明した上で、違反を検出する方法を示し、段階的な切り替えとデータ準備完了の証明方法を定義してください。
面接官がテストしているポイント
- 宣言されたビジネス上の意図と、無効な書き込みに対するデータベースの拒絶を区別できるか。
NOT ENFORCEDは書き込みをチェックしないため、実行時の完全な整合性保証にはならないことを説明できるか。- 過去データのスキャン、増分監視、修復バッチ、および最終切り替えゲートを設計できるか。
- ダンプ、リストア、複数サービスにまたがるライター、ロールバック、およびクライアント互換性のリスクを特定できるか。
- 未検証の制約をクエリオプティマイザの前提条件として扱ってはならない理由を説明できるか。
回答前に確認すべき質問
- これは外部キーですか、それとも CHECK 制約ですか？違反の件数、増加率、修復の担当者は誰ですか？
- 書き込みは PostgreSQL のみに制限されていますか、それとも ETL ジョブ、バッチスクリプト、直接接続ツールがアプリケーションをバイパスしていますか？
- レガシーなクライアントは、新しい制約名、マイグレーションロック、切り替えの失敗に対応できますか？
- 最終的な切り替えでは違反件数をゼロにする必要がありますか、それともテナントごとに段階的に完了できますか？
- すべてのパーティションとウォーターマークがスキャンされたことを証明する証拠は何ですか？
30秒の回答フレームワーク
「NOT ENFORCED は、レガシーなダーティデータが残っている状態で CHECK や外部キーの意図を宣言するのに有用です。PostgreSQL は新しい書き込みに対してこれをチェックしないため、整合性の保証にはなりません。私なら、違反の完全なベースラインを確立し、新しい書き込みに対する増分チェックとアラートを実行し、過去の行を修復または隔離した上で、違反ゼロとリストア訓練を経て初めて強制状態へと切り替えます。移行期間中、クライアントやオプティマイザがこの宣言を検証済みの事実として扱ってはなりません。」
ステップごとの詳細な回答
セマンティクスとリスクを述べる
PostgreSQL 18 では、CHECK 制約および外部キー制約を NOT ENFORCED として指定できます。データベースはその宣言を保存しますが、強制された制約のように書き込みをチェックすることはありません。このメタデータは、ドキュメント化、ガバナンス、マイグレーションの調整をサポートしますが、アプリケーション、ETL、または独立したデータ品質チェックを置き換えるものではありません。
完全スキャンと増分の証拠を構築する
外部キーの場合は、アンチ結合を実行して親のない子レコードを検出します。CHECK 制約の場合は、述語の否定を実行します。スキャン時刻、スナップショットまたはウォーターマーク、パーティション範囲、および結果のダイジェストを記録します。その後、CDC、書き込みジョブ、またはデータ品質タスクで insert や update をチェックし、1 回限りの過去データスキャンによって新しいバイパス経路が見逃されないようにします。
SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
AND c.customer_id IS NULL;修復と切り替えゲートを設計する
孤立した注文を、自動修復可能、業務確認が必要、隔離が必要、のいずれかに分類します。修復処理には冪等性キー、バッチサイズ制限、停止条件を設定します。切り替える前に、完全スキャンでの違反ゼロ、クリーンな増分ウィンドウ、バックアップおよびリストア訓練の成功、すべてのライターの網羅を必須条件とします。強制マイグレーションは低トラフィックの時間帯に実行し、ロック待ちやエラー率を監視します。
リカバリと環境ドリフトに対処する
マイグレーションが失敗した場合は、NOT ENFORCED 宣言と修復済みレコードを維持し、アプリケーションのリリースとデータ品質ジョブをロールバックして、制約が強制されていると主張しないようにします。ダンプ、リストア、リードレプリカ、ディザスタリカバリクラスタ、および古いクライアントをテストします。ORM の表示だけに頼るのではなく、ターゲット環境が conenforced メタデータを正しく解釈できることを確認します。
高品質な回答例
「私は NOT ENFORCED をデータベースのガードレールではなく、マイグレーションの契約として扱います。orders と customers に対する完全なアンチ結合を実行し、パーティションとウォーターマークを記録した上で、CDC に新しい書き込みをチェックさせます。孤立レコードは自動修復、業務確認、隔離に分類し、冪等な再実行を可能にします。完全チェックと増分チェックの両方がクリーンになり、リストアテストに合格し、すべてのライターが網羅された後にのみ、低トラフィック時間帯に外部キーを強制状態へと切り替え、ロック待ちを監視します。いかなる時点においても、クライアントやクエリオプティマイザがこの宣言を整合性の証明と見なすことはありません。」
よくある間違い
- 間違い: スキーマに外部キーがあるのを見て、データの一貫性が保たれていると思い込む → 失敗の理由:
NOT ENFORCEDは書き込みをチェックしない → 修正方法: 完全および増分の品質証拠を提示する。 - 間違い: 過去のテーブルを 1 回だけスキャンする → 失敗の理由: バイパスする新しいライターが違反を生成する可能性がある → 修正方法: CDC、ETL、スクリプト、アプリケーションの書き込みをカバーする。
- 間違い: 直接強制状態に切り替える → 失敗の理由: ダーティデータやロックによってマイグレーションが中断される可能性がある → 修正方法: 修復、リハーサル、ゲート設定を行い、低トラフィック時間帯に切り替える。
- 間違い: 強制されていない宣言を最適化のヒントとして扱う → 失敗の理由: 宣言は整合性の証明ではない → 修正方法: 検証済みの統計情報とサポートされているデータベースの動作のみに依存する。
フォローアップの質問と回答
テーブル間のルールに対して、CHECK 制約で別のテーブルを参照できますか？
それに依存すべきではありません。行レベルの CHECK 制約では、他の行が変更された後にグローバルな条件を保証できず、ダンプ/リストアの順序によって欠陥が露呈する可能性があります。テーブル間のリレーションには、外部キーまたは独立したデータ品質タスクを使用することをお勧めします。
違反件数が決してゼロにならない場合はどうすればよいですか？
NOT ENFORCED を維持し、違反データを隔離領域または明示的なビジネス例外リストに配置し、増加上限、担当者、有効期限を設定します。この制約が近い将来に実行可能な契約ではなく単なるドキュメントである場合は、スキーマに含めるべきかどうかを再評価します。
アプリケーションのバリデーションで強制された外部キーを置き換えることはできますか？
移行措置または補足としてのみ可能です。複数のライター、並行性、直接スクリプトによってアプリケーションのチェックがバイパスされる可能性があるため、最終的な整合性の境界はデータベースの制約または検証可能なデータパイプラインであるべきです。
ディザスタリカバリ用レプリカがドリフトしていないことをどのように証明しますか？
プライマリとリストアされたレプリカで同じ品質クエリを実行し、ウォーターマーク、違反件数、結果のダイジェストを比較して、リストア訓練を切り替えゲートに含めます。
参考資料
- PostgreSQL ドキュメント 18: 制約
- PostgreSQL ドキュメント 18: CREATE TABLE
- PostgreSQL ドキュメント 18: リリースノート
- Greg Low: SQL面接: 64 制約の無効化と再有効化
- MockIF: SQL面接の質問 2026
回答のコツ
NOT ENFORCED は書き込みをチェックせずに制約を宣言するものであることを述べ、その上でベースライン、増分監視、修復の分類、切り替えゲート、およびリストアの証拠を提示します。
1文の要点
NOT ENFORCED は可視化されたマイグレーション契約であり、データエンジニアは実行時の強制保護を適用する前に、依然として独立した証拠を必要とします。