問72
頻出度★★★
関係データベースで管理している“口座”表，“顧客”表及び“取引明細”表がある。新たな顧客が口座の開設と同時に1万円を入金するとき，表にデータを追加する順序として，適切なものはどれか。ここで，下線のうち実線は主キーを，破線は外部キーを表す。 口座
| 口座番号（主キー） | 口座種別 | 残高 | 顧客番号（外部キー） |
|---|
顧客
| 顧客番号（主キー） | 顧客名 |
|---|
取引明細
| 取引番号（主キー） | 取引日時 | 取引種別 | 取引金額 | 口座番号（外部キー） |
|---|
令和8年度 ITパスポート試験 問72
テクノロジ系データベース › データベース設計
This question's explanation and your answers so far.
解説
問 72
Incorrectア
This is the order Account → Customer, but the “Account” table references the “Customer” table via the foreign key “Customer Number”, so adding an account while the customer does not yet exist violates referential integrity. The customer must be registered first.
- イ
正解 A foreign key can only point to “a row whose referenced primary key already exists” (referential integrity). From the figure, the “Account” table references the “Customer” table via the foreign key “Customer Number”, and the “Transaction Detail” table references the “Account” table via the foreign key “Account Number”. Therefore the referenced side must be registered first, so the order of addition is Customer → Account → Transaction Detail. This also matches the business flow of registering a new customer → opening an account for that customer → making a deposit (transaction). Therefore the correct answer is イ.
Incorrectウ
This is the order Customer → Transaction Detail → Account, but the “Transaction Detail” table references the “Account” table via the foreign key “Account Number”. A transaction detail cannot be added while the account has not yet been registered, so this is wrong.
Incorrectエ
This is the order Transaction Detail → Account → Customer, which completely reverses the reference relationships. The first transaction detail references a nonexistent account, and the next account references a nonexistent customer, both violating referential integrity.
ここがポイント
- A foreign key can only point to a row whose referenced primary key exists (referential integrity). The principle for adding data is “the referenced (parent) table first, the referencing (child) table afterward”.
- Read the reference direction between tables from the solid/dashed underlines of the primary keys and foreign keys in the figure, and arrange them in parent → child order.
用語