Skip to content

Instantly share code, notes, and snippets.

@a-suenami
Created July 29, 2019 08:21
Show Gist options
  • Save a-suenami/0e6d60643ee72f10e35cf09c274eb760 to your computer and use it in GitHub Desktop.
Save a-suenami/0e6d60643ee72f10e35cf09c274eb760 to your computer and use it in GitHub Desktop.
チェック制約で文字列長を制限する
CREATE TABLE check_constraint_sample (col_not_null VARCHAR(255) NOT NULL, col_nullable VARCHAR(255));
ALTER TABLE check_constraint_sample ADD CONSTRAINT constraint1 CHECK (LENGTH(col_not_null) > 0);
ALTER TABLE check_constraint_sample ADD CONSTRAINT constraint2 CHECK (LENGTH(col_nullable) > 0);
-- 正常終了する
INSERT INTO check_constraint_sample (col_not_null, col_nullable) VALUES ('not null', 'not null');
INSERT INTO check_constraint_sample (col_not_null, col_nullable) VALUES ('not null', null);
-- 以下、すべてエラー
INSERT INTO check_constraint_sample (col_not_null, col_nullable) VALUES ('not null', '');
INSERT INTO check_constraint_sample (col_not_null, col_nullable) VALUES (null, 'not null');
INSERT INTO check_constraint_sample (col_not_null, col_nullable) VALUES ('', 'not null');
@a-suenami
Copy link
Author

PostgreSQL 9.6 系でとりあえず動いた(手元に 9 系しか入ってなかった…)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment