ラベル create table の投稿を表示しています。 すべての投稿を表示
ラベル create table の投稿を表示しています。 すべての投稿を表示

2026年6月29日月曜日

SQLite3 テーブルに主キーを設定する(PRIMARY KEY)

概要

SQLite3 でカラムに主キーを設定するにはいくつか方法があるので、 この記事ではその方法について記載する。

環境
  • Windows 11
  • SQLite3 (3.53.1) Command-Line Shell

テーブル作成時にカラムに続けて主キーを指定する

例として以下のような、idを主キーとする user テーブルを作成する。

「user」テーブル
カラム名 型 制約
id text 主キー、 not null制約
name text

このテーブルを作成するためには以下のSQLを実行する。
カラム名の後にPRIMARY KEYと記述することで、そのカラムに主キーを設定出来る。 


create table user (
    id     text  primary key not null
    , name text
);

上記のSQLを実行すると、idカラムに主キーが設定される。
そのため、idカラムには重複する値を登録することは出来なくなる。

以下は、上記のテーブルにデータを登録する例。 idカラムに重複する値を登録しようするとエラーになる。 


sqlite> insert into user (id, name) values ('001', 'sample_name_1');
sqlite> insert into user (id, name) values ('002', 'sample_name_2');
sqlite> insert into user (id, name) values ('001', 'sample_name_3');
Runtime error: UNIQUE constraint failed: user.id (19)
補足

上記の方法で複数カラムを主キーとする、いわゆる「複合キー」を設定しようとするとエラーになる。 複合キーを設定するには次項の「テーブル作成時にPRIMARY KEY構文を使用する」を参照のこと。

以下は、複合キーを設定しようとしてエラーになる例。 


sqlite> create table user (
(x1...>     id     text primary key not null
(x1...>     , name text primary key
(x1...> );
Parse error: table "user" has more than one primary key

テーブル作成時にPRIMARY KEY構文を使用する

カラム名に続けてPRIMARY KEYを指定するのではなく、テーブル作成分の後半にPRIMARY KEY構文を使用して主キーを設定することも出来る。
この方法を使用することで、複数カラムを主キーとする「複合キー」を設定することが出来る。

以下は、PRIMARY KEY構文を使用して主キーを設定する例。

  1. idカラムを主キーとする場合
  2. idカラムとnameカラムを複合キーとして主キーに設定する場合


-- 1.
create table user (
  id     text not null
  , name text
  , primary key (id)
);

-- 2.
create table user (
  id     text not null
  , name text not null
  , primary key ( id, name )
);

複合キーは複数のカラムの値の組み合わせでユニークになることを保証するため、 複合キーに設定したカラムのいずれかに重複する値があっても、他のカラムの値が異なれば登録することが出来る。

以下は、複合キーを設定したテーブルにデータを登録する例。 


sqlite> create table user (
(x1...>   id text  not null
(x1...>   , name text not null
(x1...>   , primary key ( id, name )
(x1...> );
sqlite> insert into user (id, name) values ('001', 'sample_name_1');
sqlite> insert into user (id, name) values ('001', 'sample_name_2');
sqlite> insert into user (id, name) values ('001', 'sample_name_1');
Runtime error: UNIQUE constraint failed: user.id, user.name (19)

テーブル作成後に主キーを設定する

SQLite3 では、既存のテーブルに対して主キーを ALTER TABLE等で追加設定することは出来ない。 そのため、既存のテーブルに主キーを設定するには、以下の手順で行う必要がある。

  1. 新しいテーブルを作成する
  2. 既存のテーブルから新しいテーブルにデータをコピーする
  3. 既存のテーブルを削除する

詳しい作業手順は過去記事のカラムの型を変更する手順と同じになる。
結構手間がかかるので、最初のテーブル設計はきっちりやるべし。

主キーを設定するカラムには必ず NOT NULL 制約を設定する

注意事項として、SQLite3 では PRIMARY KEYを指定しただけでは NULLを入力することができてしまう。

以下はidカラムに主キーを設定したテーブルにNULLを登録する例。

「user」テーブル
カラム名 型 制約
id text 主キー
name text 

sqlite> .mode box
sqlite> .nullvalue [null]
sqlite> create table user (
(x1...>   id     text primary key
(x1...>   , name text
(x1...> );
sqlite> insert into user (id, name) values (NULL, 'sample_name_1');
sqlite> insert into user (id, name) values (null, 'sample_name_2');
sqlite> select * from user;
┌────────┬───────────────┐
│   id   │     name      │
├────────┼───────────────┤
│ [null] │ sample_name_1 │
│ [null] │ sample_name_2 │
└────────┴───────────────┘

上記の例では、idカラムに主キーを設定しているにも関わらず、 NULLを登録することが出来てしまっている。 さらに、NULLであれば複数行登録することも出来てしまっている。
そのため、主キーを設定するカラムにはNOT NULL制約を設定したほうがよい。

これは過去バージョンで主キーにNULLが登録出来てしまっていたことに起因している。 SQLite は後方互換性を重視しているため修正はせず、NULLが登録できる状態を維持する方針らしい。

また、上記の例では主キーのデータ型がtextのためこのような挙動になっているが、 integer型の主キーの場合、もうちょっと厄介な挙動をする。
そのため、主キーを設定するカラムにはNOT NULL制約は必須という考えでいたほうが安全。

参考URL

Posted by at
Labels: , , ,

2022年4月2日土曜日

SQLite3 nullを登録できないようにカラムを設定する(NOT NULL制約)

概要

sqlite3で カラムにnullを登録できないように制限するSQL。
nullを登録出来ないようにする = 値の登録が必須、となる。 sqlite では空白文字とnullが区別されることに注意すること。

nullを登録できないようにするには、テーブル作成時にカラムに対して「NOT NULL」制約を指定すればいい。

既存のテーブルに対して「NOT NULL」制約を追加する方法もあるが、かなり面倒。

環境
  • Windows 10 64bit
  • SQLite3 (3.38.0) Command-Line Shell

テーブル作成時に「NOT NULL」制約を設定する

サンプルのテーブルとして、「id」「name」カラムを入力必須とし、 「mail」カラムは入力必須ではないものとする。

「test」テーブル
カラム名 型 制約
id int primary key
not null
name text not null
mail text

このテーブルを作成するためには以下のSQLを実行する。
カラム名の後にNOT NULLと記述することで、そのカラムに「NOT NULL」制約を設定出来る。 


create table test (
    id     int  primary key not null
    , name text not null
    , mail text
);

これで「test」テーブルにデータを登録する際、 「id」カラムと「name」カラムは入力必須となる。

以下は上記のテーブルにデータを登録する例。

  1. 「mail」カラムは空でも登録出来る
  2. 「id」や「name」を空で挿入とすると「NOT NULL」制約でエラーになる
  3. 挿入だけでなく更新時も「NOT NULL」制約でエラーになる

sqlite> -- # 1.
sqlite> insert into test (id, name) values (1, 'sample_name_1');

sqlite> -- # 2.
sqlite> insert into test (id, mail) values (2, 'sample@example.com');
Runtime error: NOT NULL constraint failed: test.name (19)

sqlite> -- # 3.
sqlite> update test set (id, mail) values (null, 'sample@example.com');
Parse error: near "values": syntax error
    update test set (id, mail) values (null, 'sample@example.com');
                 error here ---^

既存のテーブルのカラムに「NOT NULL」制約を設定/解除する

既存のテーブルのカラムに「NOT NULL」制約を追加する少し手間がかかる。
と言うのも、SQLite3では ALTER TABLE で「NOT NULL」制約を設定することが出来ない。

「NOT NULL」制約を設定する具体的な方法はカラムの型を変更する方法と同じ。
解除するには設定する方法と逆の手順にすればいい。

以下は比較的簡単なテーブルでの変更例。

  • 「NOT NULL」制約を設定した状態のテーブルを別名で作成する。
  • 元のテーブルから新規作成したテーブルへデータをコピーする。
  • 元のテーブルを削除する。
  • 新規作成したテーブルの名前を変更する。

sqlite< .schema test
CREATE TABLE IF NOT EXISTS "test" (
    id     int  primary key not null
    , name text not null
    , mail text
);
sqlite< create table new_test (
   ...<     id     int  primary key not null
   ...<     , name text not null
   ...<     , mail text not null
   ...< );
sqlite< insert into
   ...<     new_test
   ...< select
   ...<     *
   ...< from
   ...<     test
   ...< ;
sqlite< drop table test;
sqlite< alter table new_test rename to test;

元のデータにnullが含まれているとinsert ... into ... の時点で「NOT NULL」制約でエラーになるので補完しておくこと。 


sqlite< .nullvalue [null]
sqlite< .mode box
sqlite< select * from test;
┌────┬──────┬────────┐
│ id │ name │  mail  │
├────┼──────┼────────┤
│ 1  │ name │ [null] │
└────┴──────┴────────┘
sqlite< insert into
   ...<     new_test
   ...< select
   ...<     *
   ...< from
   ...<     test
   ...< ;
Runtime error: NOT NULL constraint failed: new_test.mail (19)

上記は簡単な実行例だが、外部キー制約や参照しているビューなどがあるためさらに複雑な手順になる。 その手順については以下を参照のこと。

その他 実行例で使用しているコマンドはこちら。

参考URL

Posted by at
Labels: , ,
登録: 投稿 (Atom)