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

Posted at 2023-12-28

背景・目的

Entity Relationship Diagram for primary keys and foreign keysが発表されましたので、試してみます。

まとめ

下記のようなテーブルを使ってReleationshipの機能を試してみました。
Releationshipは、PKとFKにより作られます。
PKとFKには整合性制約はありません。
下記のような内容が確認できます。

概要

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

実践

下記のようなテーブルを使って試してみます。

事前準備

スキーマを作成します。
```
CREATE CATALOG test

SHOW CATALOGS
```

スキーマ（DB）を作成します。

CREATE SCHEMA test.retail

SHOW SCHEMAS IN test

テーブルを作成

顧客、商品、注文、注文明細を作成します。

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

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

作成した制約は、下記のSQLで確認できます。

SELECT *
FROM 
 system.information_schema.CONSTRAINT_COLUMN_USAGE
WHERE table_catalog='test'
AND table_schema='retail'

リレーションの確認

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

Catalog Explorerで作成したテーブルを選択します。
作成したテーブルのカラムにPKとFKがついていることがわかります。画面右上の「View relationships」をクリックします。
リレーションが確認できました。

制約の確認

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

データを登録します。

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

顧客と注文を確認します。

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;

注文と注文明細を確認してみます。

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;

商品と商品明細を確認してみます。

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;

顧客のレコード（customer_id=1）を削除してみます。注文テーブルから参照されているのでRDBなどでは削除ができませんが消せました。ドキュメントに書いてあるように強制力はないようです。
```
DELETE FROM test.retail.customers WHERE customer_id=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;

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

考察

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

参考

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up