【Databricks】Entity Relationship Diagram for primary keys and foreign keysを試してみた

# 背景・目的
[Entity Relationship Diagram for primary keys and foreign keys](https://docs.databricks.com/en/release-notes/product/2023/december.html#entity-relationship-diagram-for-primary-keys-and-foreign-keys)が発表されましたので、試してみます。

# まとめ
- 下記のようなテーブルを使ってReleationshipの機能を試してみました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/dd160019-cf1c-d1e0-32b6-186fdaa4896d.png)

- Releationshipは、PKとFKにより作られます。
- PKとFKには整合性制約はありません。
- 下記のような内容が確認できます。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/798d2eb5-59c2-6301-fe76-107a774e4b31.png)

# 概要
- Unity Catalog テーブルのフィールドで使用。
- 主キーと外部キーは情報提供のみを目的としており、強制されない。
- 外部キーは、別のテーブルの主キーを参照する必要がある。
- テーブルの作成時にテーブル仕様句の一部として宣言可能。
- 既存のテーブルに制約を追加することも可能
- CTAS ステートメントでは許可されない。

# 実践
下記のようなテーブルを使って試してみます。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/59b4e075-6d08-cd3f-ecaf-087a125d602f.png)

## 事前準備
1. スキーマを作成します。
    ```
    CREATE CATALOG test

    SHOW CATALOGS
    ```

1. スキーマ（DB）を作成します。
    ```
    CREATE SCHEMA test.retail

    SHOW SCHEMAS IN test
    ```

## テーブルを作成
1. 顧客、商品、注文、注文明細を作成します。
    ```
    CREATE TABLE test.retail.customers (
        customer_id INT ,
        name VARCHAR(100),
        address VARCHAR(255),
        phone VARCHAR(50),
        CONSTRAINT customers_pk PRIMARY KEY (customer_id)
    )
    CREATE TABLE test.retail.products (
        product_id INT ,
        product_name VARCHAR(100),
        price DECIMAL(10, 2),
        stock INT,
        CONSTRAINT product_pk PRIMARY KEY (product_id)
    );
    CREATE TABLE test.retail.orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        order_date DATE,
        FOREIGN KEY (customer_id) REFERENCES test.retail.customers(customer_id)
    );
    CREATE TABLE test.retail.order_details (
        order_id INT,
        product_id INT,
        purchase_date DATE,
        purchase_price DECIMAL(10, 2),
        quantity INT,
        PRIMARY KEY (order_id, product_id),
        FOREIGN KEY (order_id) REFERENCES test.retail.orders(order_id),
        FOREIGN KEY (product_id) REFERENCES test.retail.products(product_id)
    );

    ```
1. 作成したテーブルの一覧を確認します。
    ```
    SHOW TABLES IN test.retail
    ```
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/c15a10f8-95c8-39c9-0588-645587fe3b1a.png" width=400 />

1. 作成したテーブルの詳細を確認するには、下記のコマンド　
    ```
    DESCRIBE DETAIL test.retail.customers
    ```

1. 作成した制約は、下記のSQLで確認できます。
    ```
    SELECT *
    FROM 
     system.information_schema.CONSTRAINT_COLUMN_USAGE
    WHERE table_catalog='test'
    AND table_schema='retail'
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/b37b5cb8-df11-26d3-5d64-983c6308ebf1.png)

## リレーションの確認
先ほど作成したリレーションを確認します。

1. Catalog Explorerで作成したテーブルを選択します。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/fc88962a-d9f0-c835-369b-db3b9cedbf6a.png" width=300 />

1. 作成したテーブルのカラムにPKとFKがついていることがわかります。画面右上の「View relationships」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/485743a4-5258-4b50-1b93-9e5a466c4087.png)

1. リレーションが確認できました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/798d2eb5-59c2-6301-fe76-107a774e4b31.png)

## 制約の確認
主キーと外部キーの制約では、強制はないということなので試してみます。

1. データを登録します。
    ```
    --顧客
    INSERT INTO test.retail.customers (customer_id, name, address, phone)
    VALUES
    (1, '山田太郎', '東京都渋谷区...', '090-XXX-XXX'),
    (2, '鈴木花子', '大阪府北区...', '080-YYY-YYYY');
    
    -- 商品
    INSERT INTO test.retail.products (product_id, product_name, price, stock)
    VALUES
    (101, 'りんご', 100.00, 50),
    (102, 'バナナ', 80.00, 100);
    
    -- 注文
    INSERT INTO test.retail.orders (order_id, customer_id, order_date)
    VALUES
    (1001, 1, '2023-01-01'),
    (1002, 2, '2023-01-02');
    
    -- 注文明細
    INSERT INTO test.retail.order_details (order_id, product_id, purchase_date, purchase_price, quantity)
    VALUES
    (1001, 101, '2023-01-01', 100.00, 1),
    (1001, 102, '2023-01-01', 80.00, 2),
    (1002, 102, '2023-01-02', 80.00, 3);
    
    ```
1. 顧客と注文を確認します。
    ```
    SELECT c.customer_id, c.name, o.order_id, o.order_date
    FROM test.retail.customers c
    JOIN test.retail.orders o ON c.customer_id = o.customer_id;
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/cf58a2d5-b468-11da-0a19-952ddac0e883.png)


1. 注文と注文明細を確認してみます。
    ```
    SELECT o.order_id, o.order_date, od.product_id, od.purchase_price, od.quantity
    FROM test.retail.orders o
    JOIN test.retail.order_details od ON o.order_id = od.order_id;    
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/70cabb1e-b1cc-ef92-b14f-70fcdf997295.png)

1. 商品と商品明細を確認してみます。
    ```
    SELECT p.product_id, p.product_name, od.order_id, od.quantity
    FROM test.retail.products p
    JOIN test.retail.order_details od ON p.product_id = od.product_id;
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/6d57d3b2-735d-a45d-9f8b-84d682941254.png)

1. 顧客のレコード（customer_id=1）を削除してみます。注文テーブルから参照されているのでRDBなどでは削除ができませんが消せました。ドキュメントに書いてあるように強制力はないようです。
    ```
    DELETE FROM test.retail.customers WHERE customer_id=1;
    ```
1. もう一度、顧客と注文を確認してみます。customer_id=1の山田さんは見えません。
    ```
    SELECT c.customer_id, c.name, o.order_id, o.order_date
    FROM test.retail.customers c
    JOIN test.retail.orders o ON c.customer_id = o.customer_id;
    ```
    
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/fe976a52-4043-a3dd-2825-89524253e5d9.png)

1. 注文にはcustomer_id「1」が残っています。
    ```
    SELECT * FROM test.retail.orders
    ```

    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/6b9d058d-5daa-d0dc-3d43-87f45cd0ea4b.png)


# 考察
今回、Relation Diagramを確認してみました。ジョインキーを考えながらSQLを実行するので嬉しい機能ですね。
今後の期待としては、この情報を使用して、SQL Editorでジョインキーを間違えていた場合に警告を出してくれると嬉しいですね。


# 参考

https://docs.databricks.com/ja/tables/constraints.html#declare-primary-key-and-foreign-key-relationships

https://qiita.com/taka_yayoi/items/04c713210fca7432b871