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

2026年6月29日月曜日

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

概要

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

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

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

サンプルのテーブルとしてuserテーブルを作成、 id を主キーとする。

「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句を使用して主キーを設定する例。

  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)