データ入力ツール⑥(ExcelでRDBその25)

ExcelでRDB（リレーショナルデータベース）を操作してみようという連載のその25です。前回の記事はこちらです。

hirocom777.hatenadiary.org

外部キー制約に対応する

前回はNOT NULL制約、一意制約に対応しました。

今回は外部キー制約に対応してみようと思います。

外部キー制約とは

以前、外部キーなるのを紹介しました。

以下に再掲すると

外部キー

RDBではテーブルから他のテーブルとの関係を持つことができるのですが、この時に関係先のテーブルの主キーを参照するのです。テーブルに関係先テーブルの主キーを記述する項目を設けておいて、その項目を使って関係先テーブルのレコードを特定するってことです。この項目を『外部キー』って言います。

つまり外部キーは、関係先テーブルの主キーにあるいずれかの値になります。当然主キーにない値は指定できません（もちろん空欄でもいけません。）。これが外部キー制約です。

機能を考える

外部キー制約を実現するには、参照している主キーを検索して値が存在するかを確認すればいいとになります。前回は入力される主キーのカラムを検索して、入力値と同じ値がないかを確認しました。今回は参照する主キーのカラムを検索して、入力値と同じ値があるかを確認します。そこで、以下の関数を考えてみました。

'外部キー制約を確認します
Public Function CheckForeignKey(tableName As Variant, columnName As Variant, keyData As Variant) As Boolean
  '外部キーが指定されていない場合
  If keyData = "" Then
    MsgBox "外部キーが指定されていません"
    Exit Function
  End If
  '参照先テーブルに接続
  Dim db As New clsAccessDbase
  db.dbFileName = Me.Range("FileName").Value
  db.dbTableName = tableName
  '外部キーが参照先の主キーにあるか確認
  Dim sqlCmd As String
  sqlCmd = columnName & " = '" & keyData & "'"
  If db.SelectRecord(sqlCmd) = 0 Then
    MsgBox "外部キーが不正です"
    Exit Function
  End If
  '判定OK
  CheckForeignKey = True
End Function

参照先のテーブルとカラムを指定して、設定したデータが外部キーにあるかを確認します。主キーの確認とは違って確認先が別のテーブルとなるため、別の関数にまとめました。この関数をデータ登録するプロシージに組み込むと、以下の様になりました。

'データ登録を実行します
Public Sub cmdDataEntry_Click()
Dim inputData() As Variant
Dim i As Long
  If MsgBox("データを登録します", vbYesNo) = vbNo Then Exit Sub
  Dim db As New clsAccessDbase
  db.dbFileName = Me.Range("FileName").Value
  db.dbTableName = Me.Range("TableName").Value
  '登録データを設定します
  ReDim inputData(UBound(db.dbFieldList))
  For i = 0 To UBound(inputData)
    With Me.Range("columnList")
      '主キーの制約を確認します。
      If .Offset(1 + i, 2) = "主キー" Then
        If .Offset(1 + i, 3) = "" Then
          MsgBox "主キーが指定されていません"
          Exit Sub
        End If
        Dim sqlCmd As String
        sqlCmd = .Offset(1 + i, 0) & " = " & .Offset(1 + i, 3)
        If db.SelectRecord(sqlCmd) <> 0 Then
          MsgBox "主キーがユニークではありません"
          Exit Sub
        End If
      End If
      '主キーの制約を確認します。
      If .Offset(1 + i, 2) = "外部キー" Then
        If Not CheckForeignKey(db.dbRelatedTable(i), db.dbRelatedColumn(i), db.dbFieldList(i)) Then Exit Sub
      End If
      inputData(i) = .Offset(1 + i, 3)
    End With
  Next
  'データを登録します
  db.AddRecord inputData
  Me.Range("RecordCount").Value = db.dbRecordCount
  MsgBox "データを登録しました"
End Sub

外部キーを設定してあるサンプルファイル（売上.accdb）を用意しました。以下の構成になっています。

売上明細テーブル（売上IDが主キー、商品IDが外部キー）