背景・目的

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でジョインキーを間違えていた場合に警告を出してくれると嬉しいですね。

参考