カテゴリー: SQL

今、実は「基本情報技術者試験」に向け、勉強してます。

んで、ちょうどデータベース分野について調べてました。

 

 

前回と内容がかぶるんですが、

今回は一意性制約(UNIQUEキー制約)について学んだことを書きますー！

 

 

一意性制約(UNIQUEキー制約)とは、、、

• NULL以外の各行を一意に制約する。
• ただし、NOT NULLではないためNULLを許します。
  →NULLが複数行存在することもあります。

 

私「NULL以外を一意にするだけで、NULL許すんだー、結構ゆる〜い。」

そこで気になる記事を見つけました。

 

複合一意キーにおいてすべてのキー列に対して NULL を持つ行も同様に複数持つことが可能。

ただし、1つ以上のキー列に対して NULL を持ち、その他のキー列に対して同じ組合せの値を持つ 2つの行は制約違反となる。(※)

一意キーは 1 つの表に 複数定義してもよい。

つまり

• 単一列における一意キー制約において NULL が存在して NULL を INSERT してもは制約に違反しない。
• 複合列における一意キー制約において
  (NULL,NULL) ⇔ (NULL,NULL) は制約に違反しないが
  (NULL,1000) ⇔ (NULL,1000) は一意キー制約違反となる。
  (1000,NULL,NULL) ⇔ (1000,NULL,NULL) も同様に違反となる。 (※)

https://www.shift-the-oracle.com/constraint/

 

 

ん？

よくわかんないのでやってみる。(バカです、はい)

※大好きなphpPgAdminでやってます。

 

CREATE TABLE id_number_unique_key_table(
  id integer UNIQUE,
  number integer UNIQUE
);

INSERT INTO id_number_unique_key_table
VALUES(NULL, NULL);

INSERT INTO id_number_unique_key_table
VALUES(NULL, NULL);

 

うんうん、いける！

 

カラムid,number両方において、同じNULLです。

一見、ユニークじゃないのにいいの？ってなるけど、

一意性制約はNULL以外に関しての制約なのでいいのです。

 

前回の復習をすると、ユニークだけどNULL(プライマリキーではない)だから、

レコードの識別ができないため、削除/編集ができない。

 

 

 

しかし、以下の文は一意性制約違反です。

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

 

( ｰ`дｰ´)/ダメｯ　てされる。

カラムnumberにおいて、1000,1000でかぶってますもんね。

一意ではないです。

NULLでもないです。

だから怒られちゃうんです。

 

 

 

もちろんこれはできます！

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

INSERT INTO id_number_unique_key_table
VALUES(NULL, 2000);

カラムid.number両方とも、NULLかユニークです。

ユニークキー(一意性制約)はNULLかユニークであれば違反ではない！

 

PhpPgAdminって便利ですよね。

でも、私はたまに以下のことを感じていました。

各レコードの編集/削除が出来るときと出来ないときがある。

この違いなんだ？

 

 

そこでタイミングよく、敏腕上司が教えてくれました。

 

上司「主キーがあればレコード識別ができるから、GUIで編集/削除できる」

 

私「すんごい！本当だー！しらなかったー！さすが●●さん！」

ちょっと考えると、当たり前のことだなと理解しましたwww

 

上司「ブログのネタにできるね」

 

以上、こんな会話がありました。早速ネタにします。

ありがとう●●さん。

 

 

そこで自分でいろいろ試してみました。

参考：https://www.postgresql.jp/document/9.4/html/ddl-constraints.html

 

その前に、、、

データベースは整合性を保つための整合性制約があります。

<整合性制約>

・一意性制約(ユニーク制約)

・参照制約(外部キー制約)

・非NULL制約(NOT NULL制約)

の3つがあります。

 

 

私「主キーはユニークだから一意性制約か！じゃあ、uniquであればいいのか！」

いつも通り、先走る私。

CREATE TABLE id_unique_table(
 id integer UNIQUE,
 name text
);

INSERT INTO id_unique_table
VALUES(1, test1);
INSERT INTO id_unique_table
VALUES(2, test2);
INSERT INTO id_unique_table
VALUES(3, test3);
INSERT INTO id_unique_table
VALUES(NULL, 'null');

 

一意性制約のマーク(「１」)が付いています

 

ん？

id=NULLのところは編集/削除できんやーん＞＜

 

 

ってことは….NOT NULLも必要なのか！

さっきのCREATE TABLE文に非NULL制約を追加

CREATE TABLE id_not_null_unique_table(
 id integer UNIQUE NOT NULL,
 name text
);

INSERT INTO id_not_null_unique_table
VALUES(1, 'test1');
INSERT INTO id_not_null_unique_table
VALUES(2, 'test2');
INSERT INTO id_not_null_unique_table
VALUES(3, 'test3');

※INSERT INTO id_not_null_unique_table
VALUES(null, ‘null’); は非NULL制約に反するためINSERTできませんでした。

 

想定通り、非NULL制約が追加されました。

 

 

私「なるほど。id=NULLのレコードが複数あったらレコードを識別できない。

ってことは、主キーは一意性制約と非NULL制約が必要なんだ！」

 

 

つまり、

CREATE TABLE テーブル(
 id integer UNIQUE NOT NULL,
 name text
);

と

CREATE TABLE テーブル(
 id integer PRIMARY KEY,
 name text
);

は同じ！

 

試しに、後者を実行。

PRIMARY KEY宣言したら、勝手にNOT NULLが入ってた！

 

 

 

ユニークでNOT NULLじゃないと、

そりゃー誰でも識別できんですわ。

 

 

あまりないことだと思いますが、

カラムにスペースや空白が入ってるレコードを取得したい時

 

SELECT *

FROM テーブル

WHERE TRIM(カラム) = ”;

 

※PostgreSQL, MySQL,Oracleでは使えます。SQLServerdは異なる模様

• TRIM….左右の空白を除去した文字列を取得できる
• LTRIM….左の空白を除去した文字列を取得できる
• RTRIM….右の空白を除去した文字列を取得できる

 

以下では、対応できない。

SELECT *

FROM テーブル

WHERE カラム IS NULL;

 

新しいSQL語彙を知ったので書きます！

 

・OVER句

・ROW_NUMBER()

 

 

まずはOVER句!

区間ごとに分割したり、並び替えたりできるやーつ

GROUP BYと何が違うんかな？

(((((( や っ て み よ う )))))

 

こんなテーブル(niku29)があったとして

id	class_id	name
1	1	カルビ
1	2	ホルモン
2	1	せせり
3	1	豚バラ

※idとclass_idのテーブル用意して結合するとかだと思うんですがここでは触れないでくださいw

 

牛(id=1)、鳥(id=2)、豚(id=3)の各種類の数を知りたい場合、

GROUP BYを使うと

SELECT
id,count(class_id)
FROM
niku29
GROUP BY id;

こんな感じ。すなお。

id	count
1	2
3	1
2	1

 

 

OVER句を使うと、

SELECT
id,count(class_id) OVER (PARTITION BY id)
FROM
niku29;

こんな感じ?

id	count
1	2
1	2
2	1
3	1

 

しかし、これだとid=1について２行同じレコードでてきちゃいます。

 

 

 

そこで、ROW_NUMBER()を使い、idごとに行数振り、

１行目だけ取ってくるやり方で見やすくします。

とりあえず、

SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id)
FROM
niku29;

で行を表示してみる。

row_num	id	count
1	1	2
2	1	2
1	2	1
1	3	1

イイカンジ、もうちょい。

 

 

んで、１行目だけ取ってきて
SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id)
FROM
niku29
WHERE row_num = 1;

ERROR:  列"row_num"は存在しません

え、あるやん。

 

そこで、

SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id)
FROM
niku29
WHERE row_number() OVER (PARTITION BY id) = 1;

ERROR:  WHERE句ではウィンドウ関数を使用できません

使えないらしい・・・。

ウィンドウ関数 → row_number()、rank()とか….

 

 

すんごいネストするけどこれならできる！

SELECT
id,count
FROM
(SELECT
row_number() OVER (PARTITION BY id) AS row_num,
id,
count(class_id) OVER (PARTITION BY id) AS count
FROM
niku29) niku
WHERE row_num = 1;

id	count
1	2
2	1
3	1

 

 

うーん、しかし例が悪かった。

もっとOVER,ROW_NUMBER()が役立つ時がきっとある！

 

まとめ

・WHERE句にWindow関数(ROW_NUMBERとか)は使えない。

 

 

シーケンス操作関数とは…

参考：ここ

“PostgreSQLのシーケンスオブジェクトに対し演算を行う関数。シーケンスオブジェクト（シーケンスジェネレータとも単にシーケンスとも呼ばれます）とはCREATE SEQUENCEで作成される1行の特別なテーブルです。”

テーブルなんですねえ。

 

シーケンス関数一覧

• nextval(regclass)　…
• currval(regclass)    ….nextval後に
• lastval()
• setval(regclass, bright)
• setval(regclass, bright, boolean)

 

 

＼よくわかんないからやってみた。／

・SELECT currval(‘test_seq’::regclass);
・SELECT lastval();

※それぞれを別セッションで実行

currvalとlastvalはnextvalの後とか同じセッション内で実行しないと

「セッションでまだ定義されてません」

ってでますね。

(まとめ)

currvalとlastvalは同セッション内にてnextvalで定義した後に機能する。

nextvalで得られた値を返す。

　is_calledフラグが「はい」　→　最終値 + 増加数

   is_calledフラグが「いいえ」　→　最終値

　is_calledフラグ…..呼ばれた時に増加数分増加する/しない

 

 

currvalとlastvalって何が違うんやろ？

・lastval…シーケンスを指定するところがないです！

同セッション内で複数のシーケンスをnextvalした時、一番最初にnextvalしたシーケンスの返り値を得ることができました。

・currval…シーケンスが指定できますね。

 

 

 

regclassってなん？

参考：ここ

OID(オブジェクト識別子)っていうシステムテーブルのプライマリーキーの別名らしい。

よくわからんので、わかり次第まとめます！

 

 

シーケンスって便利ですよね。

SQLでもできるし、phppgadminを使えばGUIでも操作できる！

 

しかし、シーケンス設定っていまいちよくわからないのでまとめます。

 

• phppgadminでのシーケンスの作成

phppgadminでシーケンスを作成しようとするとこんな画面がでます。

名前：シーケンスの名前をつけます。

よく見るのは「 テーブル名_カラム名_seq」ですね。

増加数：直前の数値からいくつ増加するか。

最小値：デフォルトは1。

最大値：MAX値

開始値：デフォルトは最小値と同じ。

キャッシュ値：メモリに格納しておくシーケンス番号の量を指定します。

これによりアクセスを高速にすることができます。

最小値は１(デフォルト)

Can cycle?：番号が最大値を超えた場合に最小値に戻るかどうか。

「いいえ」(デフォルト)が指定された場合、シーケンスの限界値に達した後の

　　　　　　 nextval呼び出しは全てエラーになる。

ログカウント：わからん！誰か教えてください。

Will increment last value before returning next value (is_called)?：

訳）次の値を返す前に最終値を増加しますか？

 

 

例えば、

増加数：1

最小値：1

開始値：1

Will increment last value before returning next value (is_called)?：いいえ

このシーケンスは１から始まり、2,3,4,….と１ずつ増加します。

 

 

 

• phppgadminでのシーケンスの初期化

１.「リセット」をクリック。//最終値が１となる

２.「Restart」をクリック。//Will increment last value before returning next value (is_called)?が「いいえ」となる

 

 

• SQLでのシーケンスの初期化

SELECT setval (‘シーケンス名’,  1,  false);

第２引数：最終値

第３引数：値を返す前に増加させるか<</Will increment last value before returning next value (is_called)?>>　 true(増加する)/false(増加させない)