第3章の動作確認
完了要件
psqlでossdbにログインして、SQL言語の基本であるデータ定義、データ操作、問い合わせの動作を確認します。
3.1 データ定義
以下を確認します。
- CREATE TABLE文で表を定義する
- 制約条件に違反するデータは格納できない
- ALTER TABLE文で表定義を変更する
- DROP TABLE文で表を削除する
CREATE TABLE文(表の定義)
[postgres@host1 ~]$ psql ossdb
psql (13.14)
"help"でヘルプを表示します。
ossdb=# CREATE TABLE prod ( --CREATE TABLE文
prod_id INTEGER NOT NULL,
prod_name TEXT NOT NULL,
price INTEGER DEFAULT 0 CHECK(price >= 0),
PRIMARY KEY (prod_id));
CREATE TABLE
ossdb=# \d prod ※prod表の定義を確認
テーブル"public.prod"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
-----------+---------+----------+---------------+------------
prod_id | integer | | not null |
prod_name | text | | not null |
price | integer | | | 0
インデックス:
"prod_pkey" PRIMARY KEY, btree (prod_id)
Check 制約:
"prod_price_check" CHECK (price >= 0)
ossdb=# INSERT INTO prod VALUES --INSERT文で3行追加
(1, 'みかん', 50),
(2, 'りんご', 70),
(3, 'メロン' , 100);
INSERT 0 3
ossdb=# INSERT INTO prod VALUES (3, 'バナナ', 30); --エラー
ERROR: 重複したキー値は一意性制約"prod_pkey"違反となります
DETAIL: キー (prod_id)=(3) はすでに存在します。
ossdb=# INSERT INTO prod VALUES (4, 'バナナ', -30); --エラー
ERROR: リレーション"prod"の新しい行は検査制約"prod_price_check"に違反しています
DETAIL: 失敗した行は(4, バナナ, -30)を含みます
ossdb=# INSERT INTO prod(prod_id, price) VALUES (5, 30); --エラー
ERROR: リレーション"prod"の列"prod_name"のNULL値が非NULL制約に違反しています
DETAIL: 失敗した行は(5, null, 30)を含みます
ossdb=# INSERT INTO prod(prod_id, prod_name) VALUES (6, 'バナナ'); --OK
INSERT 0 1
ossdb=# SELECT * FROM prod; --SELECT文でprod表の内容を確認
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
2 | りんご | 70
3 | メロン | 100
6 | バナナ | 0 ※price列はデフォルト値 0
(4 行)
ossdb=#CREATE TABLE文で、各種の制約条件が付いたprod表が作成されていることを確認してください。また、制約条件に違反する行を追加しようとするとエラーになることを確認してください。
ossdb=# CREATE TABLE customer ( --CREATE TABLE文
customer_id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL);
CREATE TABLE
ossdb=# \d customer
テーブル"public.customer"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
---------------+---------+----------+---------------+------------
customer_id | integer | | not null |
customer_name | text | | not null |
インデックス:
"customer_pkey" PRIMARY KEY, btree (customer_id)
ossdb=# INSERT INTO customer VALUES
(1, '佐藤商事'),
(2, '鈴木物産'),
(3, '高橋商店');
INSERT 0 3
ossdb=# SELECT * FROM customer;
customer_id | customer_name
-------------+---------------
1 | 佐藤商事
2 | 鈴木物産
3 | 高橋商店
(3 行)
ossdb=# CREATE TABLE orders ( --CREATE TABLE文
order_id INTEGER UNIQUE,
order_date DATE,
customer_id INTEGER REFERENCES customer(customer_id),
prod_id INTEGER,
qty INTEGER,
FOREIGN KEY (prod_id) REFERENCES prod(prod_id));
CREATE TABLE
ossdb=# \d orders
テーブル"public.orders"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
-------------+---------+----------+---------------+------------
order_id | integer | | |
order_date | date | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
インデックス:
"orders_order_id_key" UNIQUE CONSTRAINT, btree (order_id)
外部キー制約:
"orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
"orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
ossdb=# INSERT INTO orders VALUES
(1, '2024-04-01', 1, 1, 10),
(2, '2024-04-01', 2, 2, 5),
(3, '2024-04-01', 3, 3, 8);
INSERT 0 3
ossdb=# INSERT INTO orders VALUES (3, '2024-04-02', 1, 2, 3); --エラー
ERROR: 重複したキー値は一意性制約"orders_order_id_key"違反となります
DETAIL: キー (order_id)=(3) はすでに存在します。
ossdb=# INSERT INTO orders VALUES (4, '2024-04-02', 4, 1, 3); --エラー
ERROR: テーブル"orders"への挿入、更新は外部キー制約"orders_customer_id_fkey"に違反しています
DETAIL: テーブル"customer"にキー(customer_id)=(4)がありません
ossdb=# INSERT INTO orders VALUES (5, '2024-04-02', 3, 4, 3); --エラー
ERROR: テーブル"orders"への挿入、更新は外部キー制約"orders_prod_id_fkey"に違反しています
DETAIL: テーブル"prod"にキー(prod_id)=(4)がありません
ossdb=# INSERT INTO orders(order_id, order_date) VALUES(6, '2024-04-02'); --OK
INSERT 0 1
ossdb=# SELECT * FROM orders;
order_id | order_date | customer_id | prod_id | qty
----------+------------+-------------+---------+-----
1 | 2024-04-01 | 1 | 1 | 10
2 | 2024-04-01 | 2 | 2 | 5
3 | 2024-04-01 | 3 | 3 | 8
6 | 2024-04-02 | | |
(4 行)
ossdb=#CREATE TABLE文で、customer表とorders表が作成されていることを確認してください。また、orders表に外部キー制約に違反する行を追加しようとするとエラーになることを確認してください。
ALTER TABLE文(表定義の変更)
ossdb=# ALTER TABLE customer --ALTER TABLE文
ADD COLUMN address TEXT;
ALTER TABLE
ossdb=# \d customer
テーブル"public.customer"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
---------------+---------+----------+---------------+------------
customer_id | integer | | not null |
customer_name | text | | not null |
address | text | | |
インデックス:
"customer_pkey" PRIMARY KEY, btree (customer_id)
参照元:
TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
ossdb=# SELECT * FROM customer;
customer_id | customer_name | address
-------------+---------------+---------
1 | 佐藤商事 |
2 | 鈴木物産 |
3 | 高橋商店 |
(3 行)
ossdb=# ALTER TABLE customer --ALTER TABLE文
DROP COLUMN address;
ALTER TABLE
ossdb=# \d customer
テーブル"public.customer"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
---------------+---------+----------+---------------+------------
customer_id | integer | | not null |
customer_name | text | | not null |
インデックス:
"customer_pkey" PRIMARY KEY, btree (customer_id)
参照元:
TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
ossdb=#ALTER TABLE文のADD COLUMNでcustomer表に新しいaddress列が追加されていること、追加されたaddress列の値がNULL値になっていることを確認してください。また、DROP COLUMNでaddress列が削除されることを確認してください。
DROP TABLE文(表の削除)
DROP TABLE prod; --エラー
ERROR: 他のオブジェクトが依存しているためテーブルprodを削除できません
DETAIL: テーブルordersに対する制約orders_prod_id_fkeyはテーブルprodに依存しています
HINT: 依存しているオブジェクトも削除するにはDROP ... CASCADEを使用してください
ossdb=# TRUNCATE customer; --エラー
ERROR: 外部キー制約で参照されているテーブルを削除できません
DETAIL: テーブル"orders"は"customer"を参照します。
HINT: 同時にテーブル"orders"がtruncateされました。TRUNCATE ... CASCADEを使用してください。
ossdb=# DROP TABLE orders, prod; --DROP TABLE文
DROP TABLE
ossdb=# \d
リレーション一覧
スキーマ | 名前 | タイプ | 所有者
----------+----------+----------+----------
public | customer | テーブル | postgres
public | employee | テーブル | postgres
(2 行)
ossdb=# TRUNCATE customer; --TRUNCATE文
TRUNCATE TABLE
ossdb=# SELECT * FROM customer;
customer_id | customer_name
-------------+---------------
(0 行)
ossdb=# DROP TABLE customer; --DROP TABLE文
DROP TABLE
ossdb=# \q
[postgres@host1 ~]$DROP TABLE文で表が削除されること、TRUNCATE文で表は削除されないがすべての行が削除されることを確認します。また、どちらの文も外部キー制約で参照されていると削除ができないことを確認してください。