SQL Server 番号振り

Question

SQL ServerにAテーブル、BテーブルがありINSERT INTOで保存するときに連番が自動的に振り分けられるIDENTITYを使用していました。
しかし私の作っているものには思っていた動きをしてくれないので行き詰りました。

Aテーブルには、ID, 名前, 性別, があるとします。
Bテーブルには、ID, 名前, 顔写真, があるとします。

名前、性別はNOT NULLで、顔写真はNULLでも可能とします。

例で登録を3件実行すると、

Aテーブル Bテーブル

ID 名前 性別 ID 名前 顔写真(Image型)
1 山田 男 1 山田 あり
2 青木 女 2 青木 なし
3 井上 男 3 井上 あり

となってくれれば私の期待通りの動きです。

しかし色々動かしてみてテーブルを見てみると、連番のIDがばらついていて一致してくれません。

Aテーブル Bテーブル

ID 名前 性別 ID 名前 顔写真(Image型)
1 山田 男 1 山田 あり
2 青木 女 2 青木 なし
17 井上 男 4 井上 あり

になってたりしています。

IDはAテーブルとBテーブルの一致の為だけに振り当てているので連番じゃなくても一致さえしていればどんな数字でも構いません。
なにか良い方法はありますでしょうか。例えば、使われていなくて数が小さい数字から振り当ててくなど・・・

よろしくお願いいたします。

Answer 1

単に正規化の問題のような気がします。

Aテーブルには、ID, 名前, 性別, があるとします。
Bテーブルには、ID, 名前, 顔写真, があるとします。

Aテーブルが親でBテーブルが子の関係であれば、AテーブルのIDを
ベースにBテーブルにデータを投入することで処理ができます
A/B別々にデータ投入してIDを見比べてあってる・まちがってるでは
リレーションしているとは言えません。

Comments

[退会済みユーザー]

そのような方法があるとは知りませんでした。
親と子の関係で考えるのですね。
やり方が全然わからないので勉強してみます。

ありがとうございました。

[yambejp]

> やり方が全然わからない

データベースを扱うなら「正規化」は最低限必要な知識です。
はじめての方には感覚がなかなか馴染めないかもしれませんが
ぜひ学習して、ステップアップをはかってください

Answer 2

AテーブルとBテーブルの ID は、一致していることが必須なのですよね？
ということは、A テーブルの ID だけを IDENTITY として、

insert into A values (...);
の後で
select ident_current('A');
で最新のIDを取得、
insert into B values ({取得したID}, ...);

とすることになりますが、保証するためには B が insert されるまで A を「他のプロセスが insert しない」必要があるため、A テーブル全体を書き込み不可でロックすることになります。パフォーマンス的にどうでしょうかね。

将来のことを考えると、IDENTITY を使わずに、きちんと SEQUENCE を定義して使ったほうが良いでしょう。

余談：
なぜ B テーブルにも名前が必要なのでしょう？ 正規化で考えると、B.名前は A.名前と同じものであるはずですから、2か所に保存する意味がありません。

Comments

[退会済みユーザー]

ありがとうございます。SQLを触りだしてちょうど一週間でまだ全然わからない状態なのでSEQUENCEというのが何なのかもわからない状態ですが、勉強してみます。

また、Bテーブルに名前ですが、もしBテーブルに保存する顔写真のデータがなければIDもINSERTされなかったので名前を保存しました。（IDだけのINSERTの方法を知らないため、このようにしています）
例でいうと、ID = 2の青木さん。
Bテーブルに顔写真がない＆名前もない。となるとINSERTされない。つまりIDもINSERTされない。
といった状態になったからです。

[tacsheaven]

顔写真がないならば、Bテーブルにレコードがある必要がありません。それが問題になるのは、Aテーブルと B テーブルを inner join してしまっているからです。left outer join にすると、「B テーブルに一致するものがなければ、null とみなされる」形で返ってきますよ。
SQL を触って1週間だとまだそこまでたどり着いていないかもですが、join はとても重要（RDB が Relational である所以）ですから、ぜひ時間をかけてでもきちんと理解するようにしてください。

[退会済みユーザー]

inner joinしてます。。。left outer join にするとnullが入るなんて知りませんでした。
とても勉強になりました。
ありがとうございました。