目次
1.はじめに
皆さんこんにちは。
本書では、Databricksの主キーと外部キーについて説明していきます。
※ 主キーと外部キーは、Databricks Runtime 11.1 以降で使用できます。
※ 主キーと外部キーには、Unity Catalog と Delta Lake が必要です。
My SQL、SQL Server等のリレーショナル データベースの外部キーと主キーと同様ではなく、Databricksの外部キーと主キー は情報提供のみを目的としており、一般的なユニーク制約は適用されません。
ユニーク制約の適用無しについて例は以下の通りです。
※ 主キーに設定しても、データベースで一般的なユニーク制約はつかないことについて
① [Student]テーブルを作成して、主キーとして [studentID]を設定します。
② [studentID]として主キーを設定しても、[studentID] = 1があるのレコードを３つ挿入できます。
→ 般的なユニーク制約はつかないという結果となります。
※ 外部キーについて別のテーブルがない直を指定する場合はエラーが発生しません。
① [Student]テーブル の主キー [studentID] を参考する[studentID] 外部キー を持つ [Score]テーブル を作成します。
② [Student]テーブル がない [studentID] の値を持つ 2 つのレコードを [Score]テーブル に挿入します。
-> エラーが発生しません。
2.主キーとは
主キーはテーブル間の結合や検索を高速化するためのカラムとして定義します。
Databricksで主キーは情報提供のみを目的としており、一般的なユニーク制約は適用されません。
ただし、NOT NULL制約はあるため値を必ず設定する必要があります。
主キーは各テーブルに一つしかなく、一つ以上のカラムから構成されます。
主キーの設定は任意です。
3.外部キーとは
外部キーは他のテーブルの主キーを参照するために定義します。
Databricksで外部キーは情報提供のみを目的としており、参照される親テーブルのカラムにないデータも登録できます。
ただし、NOT NULL制約はあるため値を必ず設定する必要があります。
外部キーの設定は任意です。
4.主キーを設定
4-1.テーブルの作成時に主キーを設定
テーブル作成と同時に主キーを設定するには、以下のSQL文を使用します。
※ 文法 CONSTRAINT ～PRIMARY KEY を指定します
|
1 2 3 4 5 6 |
CREATE TABLE「カタログ名」.「スキーマ名」. 「テーブル名」( 「列名」「データ型」「制約」, 「列名」「データ型」「制約」, 「列名」「データ型」「制約」, CONSTRAINT 「主キー名」PRIMARY KEY(「列名」, 「列名」,…) ); |
※ 例
|
1 2 3 4 5 6 |
CREATE TABLE Catalog.Schema.Student( studentID INTEGER NOT NULL, studentName STRING NOT NULL, classCode INTEGER NOT NULL, CONSTRAINT s_pk PRIMARY KEY(studentID) ); |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。このコードで「Student」テーブルを作成すると同時に「studentID」カラムに「s_pk」主キーを設定します。
複数カラムを主キーを作成するには、複数のカラムを指定します。
※例
|
1 2 3 4 5 6 |
CREATE TABLE Catalog.Schema.Student( studentID INTEGER NOT NULL, studentName STRING NOT NULL, classCode INTEGER NOT NULL, CONSTRAINT s_pk PRIMARY KEY(studentID, classCode) ); |
4-2.主キーを削除
主キーを削除するには、以下のSQL文を使用します。
※ 文法
|
1 |
ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP PRIMARY KEY; |
※ 例
|
1 |
ALTER TABLE Catalog.Schema.Student DROP PRIMARY KEY; |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。作成した「Student」テーブルの主キーが削除されます。
以下のコマンドで主キー名を指定して、主キーを削除できます。
※ 文法
|
1 |
ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP CONSTRAINT 「主キー名」 |
※ 例
|
1 |
ALTER TABLE Catalog.Schema.Student DROP CONSTRAINT s_pk |
上記のコードは「Student」テーブルの「s_pk」主キーを削除します。
4-3.既存テーブルに主キーを設定
「Student」テーブルの主キーを削除する方法について説明しました。次に、以下のSQL文を使用して、既存テーブルに主キーを追加します。
※ 文法
|
1 |
ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD PRIMARY KEY (「列名」, 「列名」,…) |
※ 例
|
1 |
ALTER TABLE Catalog.Schema.Student ADD PRIMARY KEY (studentID,classCode) |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。コードは、「studentID + classCode」2つカラムを「Student」テーブルの主キーとして使用します。主キー名は「テーブル名」_pkとして自動的に追加されます。
既存テーブルに主キーを追加するには、下記のSQL文も使用できます。
このコマンドは主キー名も指定できます。
※ 文法
|
1 |
ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD CONSTRAINT 「主キー名」 PRIMARY KEY (「列名」, 「列名」,…) |
※ 例
|
1 |
ALTER TABLE Catalog.Schema.Student ADD CONSTRAINT s_pk PRIMARY KEY (studentID,classCode) |
4-4.主キーを取得
テーブルの主キーの内容を確認するには、SQL文を使用します。
※ 文法
|
1 |
SELECT * FROM 「カタログ名」.information_schema.key_column_usage WHERE table_name = '「テーブル名」' |
※ 例
|
1 |
SELECT * FROM CATALOG.information_schema.key_column_usage WHERE table_name = 'student' |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。「Student」テーブルの主キーの内容を確認できます。
5.外部キーを設定
5-1.テーブル作成時に外部キーを設定
テーブル作成と同時に外部キーを設定するには、以下のSQL文を使用します。
※ 文法
|
1 2 3 4 5 6 |
CREATE TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」( 「列名」「データ型」「制約」, 「列名」「データ型」「制約」, 「列名」「データ型」「制約」, CONSTRAINT 「外部キー名」 FOREIGN KEY (「列名」, 「列名」,…) REFERENCES 「カタログ名」.「スキーマ名」. 「テーブル名」(「列名」, 「列名」,…) ); |
※ 例
|
1 2 3 4 5 6 7 |
CREATE TABLE Catalog.Schema.Score( noNumber INT NOT NULL PRIMARY KEY, avgScore FLOAT, studentID INTEGER NOT NULL, classCode INTEGER NOT NULL, CONSTRAINT s_fk FOREIGN KEY (studentID, classCode) REFERENCES Catalog.Schema.Student(studentID, classCode) ); |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。「Score」テーブルを作成するとともに、次のように指定する「s_fk」外部キーを設定します。
「Score」テーブルの「studentID」カラムは、 「Student」テーブルの「studentID」カラムを参照します。
「Score」テーブルの「classCode」カラムは、 「Student」テーブルの「classCode」カラムを参照します。
5-2.外部キーを削除
外部キーを削除するには、下記のSQL文を使用します。
※ 文法
|
1 |
ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP CONSTRAINT 「外部キー名」; |
※ 例
|
1 2 |
ALTER TABLE Catalog.Schema.Score DROP CONSTRAINT s_fk; |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。「Student」テーブルの「s_fk」外部キーが削除されます。
5-3.既存テーブルに外部キーを設定
「Score」テーブルの外部キーを削除する方法について説明しました。既存テーブルに外部キーを設定するには、下記のSQL文を使用します。
※ 文法
|
1 2 |
ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD CONSTRAINT 「外部キー名」 FOREIGN KEY (「列名」, 「列名」,…) REFERENCES 「カタログ名」.「スキーマ名」. 「テーブル名」(「列名」, 「列名」,…) |
※ 例
|
1 2 |
ALTER TABLE Catalog.Schema.Score ADD CONSTRAINT s_fk FOREIGN KEY (studentID, classCode) REFERENCES Catalog.Schema.Student(studentID, classCode) |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。コードは、「studentID + classCode」の2つカラムを「Score」テーブルの「s_fk」外部キーとして作成します。
5-4.外部キーを取得
テーブルの外部キーの内容を確認するには、SQL文を使用します。
※ 文法
|
1 |
SELECT * FROM 「カタログ名」.information_schema.key_column_usage WHERE table_name = '「テーブル名」' |
※ 例
|
1 |
SELECT * FROM CATALOG.information_schema.key_column_usage WHERE table_name = 'student' |
上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。「Score」テーブルの外部キーの内容を確認できます。
6.まとめ
これでAzure Databricks主キーと外部キーについて説明しました。
今回の記事が少しでもDatabricksを知るきっかけや、業務のご参考になれば幸いです。
日商エレクトロニクスでは、Azure Databricksの環境構築パッケージを用意しています。
Azure DatabricksやAzure活用、マイクロソフト製品の活用についてご相談事がありましたらぜひお問い合わせください！
・Azure Databricks連載シリーズはこちら
お問い合わせはこちら この記事を読んだ方へのオススメコンテンツはこちら
この記事を書いた人
