SQLを書いていていろんな場面で「こんな文法であってくれたら！」と歯がゆい思いをすることは多々あります。 これまでに、こう拡張されてくれたらと思った内容をまとめてみました。 なお、この拡張は各社DBの“方言”とはまた別のものです。各社方言とは直交し…

DBの主キーカラムに自動連番（MySQLならAUTO_INCREMENT、SQL ServerならIDENTITY、OracleやPostgreSQLならシーケンス）を設定していると、基本的には連続した数字が付番されていくのですが、行削除もしていないのに抜け番が発生する場合があります。 INSERT…

CodeIQの問題・パズルを考えよう！（by CodeIQ×はてな）タイトル見ただけで苦労の記憶がよみがえってきた方はその時点で合格ってことでいいかもしれません。INT型の主キーを持ったテーブルがあり、1行を1アイテムとして表示できます。 CREATE TABLE items ( …

一意性(UNIQUE)制約はNULLでない値に対してだけ効く、そう思っていた頃が私にもありました… SQL ServerはNULLも一意性制約の対象になります。NULLになっている行が複数あると重複だと判定されます。SQL-92の規格書をあたってみると、これは完全な規格違反な…

SQLにブール型が定義されたのはSQL99と比較的最近のことなので、ブール型への対応にはまだばらつきがあります。 Oracle(12c), PostgreSQL(9.x) … 対応 SQL Server(2012), MySQL(5.x) … 非対応。1ビット整数のBIT型を代わりに持つ。 XOR（両辺の論理式のうち…

ツリー構造をSQLで扱うための定石はいくつか知られています。 隣接リスト最も単純で、各行が親に当たる行を自己結合で参照します。構造は単純ですが、古いSQLでは子孫を一気に取得する方法がないのでSQLアンチパターンでは「ナイーブツリー」と命名して安易…

こんなスキーマ設計を見ました。 CHECK制約で排他的サブタイプを実現する - pikio公式ブログ オブジェクト指向のプログラミングでは、クラスを継承してサブクラスを作ることはよくあります。そして、アプリケーションではデータを複数のサブクラスに分けて扱…

先に結論。DEFERRED, DEFERRABLEという単語を覚えてお帰りくださいませ。 そして先にごめんなさい。SQL ServerとMySQLはDEFERRED, DEFERRABLEを未サポートですのでこの記事の内容は使えません。さて話のはじめから。こんな条件のデータを格納したくなったと…

デッドロック発生させたと聞いただけで「うわ出たド素人」「それだけはないでしょ」「設計の仕方教わらなかったの？」と炎上3波目くらいまで装弾済みの怖い怖いお兄様方お姉様方こんばんは。案外知られていないのでこの機会に確認しておいていただきたいのは…

SQL標準にある強力なCHECK制約をPostgreSQLでも使いたいでござる。 有り体にいうと、一意性制約を付けて論理削除をしたい、というような表現になるのでしょうか。 http://sugar-tomato.hateblo.jp/entries/2013/07/02 無効化したレコードは無視して有効なレ…

SQLのテーブルにcol_A, col_Bというカラムがあるとしますよ。ここでCHECK(col_A = col_B)という制約を、col_Aにカラム制約として付けたとします。すると UPDATE my_table SET col_A = col_A + 1; なんて操作は阻止されます。当然。では、 UPDATE my_table SE…

Evolutionsに指定したSQL文は適用のとき、セミコロンで分解されて一文ずつデータベースに送信されます。 ではストアド関数に含まれるセミコロンはどうなるか。 やはりぶった切られてしまいます。当然文法エラー。ではぶった切られないようにするにはどうする…

Play! frameworkではEvolutionsファイルを書くことでデータベースへのスキーマ適用とか更新がWebからワンタッチできるようになります。ありがたいですね。sqlスクリプトファイルがスキーマ変更1件を表していて、【変更を適用するSQL文(Ups)】【変更を取り消…