【Azure Databricks】主キーと外部キーの設定方法

1.はじめに
2.主キーとは
3.外部キーとは
4.主キーを設定
5.外部キーを設定
6.まとめ
- この記事を読んだ方へのオススメコンテンツはこちら

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 を指定します

CREATE TABLE「カタログ名」.「スキーマ名」. 「テーブル名」(
  「列名」「データ型」「制約」,
  「列名」「データ型」「制約」,
  「列名」「データ型」「制約」,
  CONSTRAINT 「主キー名」PRIMARY KEY(「列名」, 「列名」,…)
);

CREATE TABLE「カタログ名」.「スキーマ名」. 「テーブル名」(

「列名」「データ型」「制約」,

CONSTRAINT 「主キー名」PRIMARY KEY(「列名」, 「列名」,…)

);

※ 例

CREATE TABLE Catalog.Schema.Student(
studentID INTEGER NOT NULL,
  studentName STRING NOT NULL,
  classCode INTEGER NOT NULL,
  CONSTRAINT s_pk PRIMARY KEY(studentID)
);

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」主キーを設定します。

複数カラムを主キーを作成するには、複数のカラムを指定します。
※例

CREATE TABLE Catalog.Schema.Student(
	studentID INTEGER NOT NULL,
	studentName STRING NOT NULL,
	classCode INTEGER NOT NULL,
	CONSTRAINT s_pk PRIMARY KEY(studentID, classCode)
);

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文を使用します。

※ 文法

ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP PRIMARY KEY;

1	ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP PRIMARY KEY;

※ 例

ALTER TABLE Catalog.Schema.Student DROP PRIMARY KEY;

1	ALTER TABLE Catalog.Schema.Student DROP PRIMARY KEY;

上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。作成した「Student」テーブルの主キーが削除されます。

以下のコマンドで主キー名を指定して、主キーを削除できます。

※ 文法

ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP CONSTRAINT 「主キー名」

1	ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP CONSTRAINT 「主キー名」

※ 例

ALTER TABLE Catalog.Schema.Student DROP CONSTRAINT s_pk

1	ALTER TABLE Catalog.Schema.Student DROP CONSTRAINT s_pk

上記のコードは「Student」テーブルの「s_pk」主キーを削除します。

4-3.既存テーブルに主キーを設定

「Student」テーブルの主キーを削除する方法について説明しました。次に、以下のSQL文を使用して、既存テーブルに主キーを追加します。

※ 文法

ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD PRIMARY KEY (「列名」, 「列名」,…)

1	ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD PRIMARY KEY (「列名」, 「列名」,…)

※ 例

ALTER TABLE Catalog.Schema.Student ADD PRIMARY KEY (studentID,classCode)

1	ALTER TABLE Catalog.Schema.Student ADD PRIMARY KEY (studentID,classCode)

上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。コードは、「studentID + classCode」2つカラムを「Student」テーブルの主キーとして使用します。主キー名は「テーブル名」_pkとして自動的に追加されます。

既存テーブルに主キーを追加するには、下記のSQL文も使用できます。
このコマンドは主キー名も指定できます。

※ 文法

ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD CONSTRAINT 「主キー名」 PRIMARY KEY (「列名」, 「列名」,…)

1	ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD CONSTRAINT 「主キー名」 PRIMARY KEY (「列名」, 「列名」,…)

※ 例

ALTER TABLE Catalog.Schema.Student ADD CONSTRAINT s_pk PRIMARY KEY (studentID,classCode)

1	ALTER TABLE Catalog.Schema.Student ADD CONSTRAINT s_pk PRIMARY KEY (studentID,classCode)

4-4.主キーを取得

テーブルの主キーの内容を確認するには、SQL文を使用します。

※ 文法

SELECT * FROM 「カタログ名」.information_schema.key_column_usage WHERE table_name = '「テーブル名」'

1	SELECT * FROM 「カタログ名」.information_schema.key_column_usage WHERE table_name = '「テーブル名」'

※ 例

SELECT * FROM CATALOG.information_schema.key_column_usage WHERE table_name = 'student'

1	SELECT * FROM CATALOG.information_schema.key_column_usage WHERE table_name = 'student'

上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。「Student」テーブルの主キーの内容を確認できます。

5.外部キーを設定

5-1.テーブル作成時に外部キーを設定

テーブル作成と同時に外部キーを設定するには、以下のSQL文を使用します。

※ 文法

CREATE TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」(
  「列名」「データ型」「制約」,
  「列名」「データ型」「制約」,
  「列名」「データ型」「制約」,
  CONSTRAINT 「外部キー名」 FOREIGN KEY (「列名」, 「列名」,…) REFERENCES 「カタログ名」.「スキーマ名」. 「テーブル名」(「列名」, 「列名」,…)
);

CREATE TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」(

「列名」「データ型」「制約」,

CONSTRAINT 「外部キー名」 FOREIGN KEY (「列名」, 「列名」,…) REFERENCES 「カタログ名」.「スキーマ名」. 「テーブル名」(「列名」, 「列名」,…)

);

※ 例

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)
);

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文を使用します。

※ 文法

ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP CONSTRAINT 「外部キー名」;

1	ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 DROP CONSTRAINT 「外部キー名」;

※ 例

ALTER TABLE Catalog.Schema.Score
DROP CONSTRAINT s_fk;

1 2	ALTER TABLE Catalog.Schema.Score DROP CONSTRAINT s_fk;

上記のコードをノートブックにコピーして、【Shift】キーと【Enter】キーを押します。「Student」テーブルの「s_fk」外部キーが削除されます。

5-3.既存テーブルに外部キーを設定

「Score」テーブルの外部キーを削除する方法について説明しました。既存テーブルに外部キーを設定するには、下記のSQL文を使用します。

※ 文法

ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」
ADD CONSTRAINT 「外部キー名」 FOREIGN KEY (「列名」, 「列名」,…) REFERENCES 「カタログ名」.「スキーマ名」. 「テーブル名」(「列名」, 「列名」,…)

1 2	ALTER TABLE 「カタログ名」.「スキーマ名」. 「テーブル名」 ADD CONSTRAINT 「外部キー名」 FOREIGN KEY (「列名」, 「列名」,…) REFERENCES 「カタログ名」.「スキーマ名」. 「テーブル名」(「列名」, 「列名」,…)

※ 例

ALTER TABLE Catalog.Schema.Score
ADD CONSTRAINT s_fk FOREIGN KEY (studentID, classCode) REFERENCES Catalog.Schema.Student(studentID, classCode)

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文を使用します。

※ 文法

SELECT * FROM 「カタログ名」.information_schema.key_column_usage WHERE table_name = '「テーブル名」'

1	SELECT * FROM 「カタログ名」.information_schema.key_column_usage WHERE table_name = '「テーブル名」'

※ 例

SELECT * FROM CATALOG.information_schema.key_column_usage WHERE table_name = 'student'

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連載シリーズはこちら

お問い合わせはこちら

この記事を読んだ方へのオススメコンテンツはこちら

アセスメントサービス

この記事を書いた人

quanna

この投稿者の最新の記事

この投稿者の記事一覧

2024年3月7日ブログ【Azure Databricks】Apache Airflow を使用して Azure Databricks ジョブを調整する
2024年2月27日ブログ【Azure Databricks】Auto Loader と Delta Live Table を組み合わせて使用してみる
2024年1月4日ブログ【Azure Databricks】Auto Loaderの概要、自動取込方法を使用してみる
2023年11月30日ブログ【Azure Databricks】「Databricks Container Servicesの設定」をしてみる

【Azure Databricks】主キーと外部キーの設定方法

1.はじめに

2.主キーとは

3.外部キーとは

4.主キーを設定

4-1.テーブルの作成時に主キーを設定

4-2.主キーを削除

4-3.既存テーブルに主キーを設定

4-4.主キーを取得

5.外部キーを設定

5-1.テーブル作成時に外部キーを設定

5-2.外部キーを削除

5-3.既存テーブルに外部キーを設定

5-4.外部キーを取得

6.まとめ

この記事を読んだ方へのオススメコンテンツはこちら

この記事を書いた人

この投稿者の最新の記事

Add comment コメントをキャンセル

03-6272-5011