192
205

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

SQLの制約の種類とその指定方法

Last updated at Posted at 2015-05-30

SQLの制約の種類

  • NOT NULL制約
  • チェック制約
  • 一意性制約(UNIQUE KEY)
  • 主キー制約 (PRIMARY KEY)
  • 参照整合性制約、外部キー制約 (FOREIGN KEY)

NOT NULL

  • NULL値を禁止
  CREATE TABLE product  
  (
    product_id       INT PRIMARY KEY ,
    product_name     VARCHAR(16) UNIQUE NOT NULL ,
    price            INT
  ) ;

CHECK

  • 条件を指定し、条件を満たさないデータを禁止
CREATE TABLE user
(
   user_id     INT PRIMARY KEY ,
   age         INT ,
   city_code   CHAR(4) ,
   gender      CHAR(1),
   CONSTRAINT agecity_check   CHECK(age >= 18 OR city_code = '0003'),
   CONSTRAINT gendercheck     CHECK(gender IN('M', 'F')))
) ;

UNIQUE KEY

  • 重複したデータを禁止
  • 複数の列に設定可能
  • NULLを禁止するわけではない
CREATE TABLE user
  (
     user_id        INT PRIMARY KEY ,
     telephone    CHAR(8)
     addreess     VARCHAR(255),
     UNIQUE (telephone, address)
  ) ;

PRIMARY KEY

  • 一意を保証
  • 重複とNULLを禁止
  • 1つのテーブルに1つ
CREATE TABLE user
  (
     user_id      INT PRIMARY KEY ,
     telephone    CHAR(8)
     age          INT,
  ) ;
  • 複合主キーの指定
CREATE TABLE price
  (
     shop_code      CHAR(4) ,
     product_code   CHAR(4) ,
     price          INT ,
     PRIMARY KEY(shop_code , product_code)
  ) ;

FOREIGN KEY

  • 他のテーブルの列を参照し、その列にないデータを禁止
CREATE TABLE order
  (
     order_id      INT PRIMARY KEY ,
     shop_code     CHAR(5) ,
     product_code  CHAR(4) ,
     number        INT ,
     date          DATE ,
     FOREIGN KEY (shop_code)
              REFERENCES shop_table(shop_code) ,
     FOREIGN KEY (product_code)
              REFERENCES product_table(product_code)
  ) ;
  • 2つの列の外部参照
CREATE TABLE item
  (
     maker_id    CHAR(10) ,
     product_id  CHAR(10) ,
     PRIMARY KEY (maker_id , product_id)
  ) ;
CREATE TABLE dealer
  (
     shop_id        CHAR(10) NOT NULL ,
     maker_id       CHAR(10) NOT NULL ,
     product_id     CHAR(10) NOT NULL ,
     PRIMARY KEY (shop_id , maker_id , product_id) ,
     FOREIGN KEY (maker_id , product_id)
            REFERENCES item(maker_id , product_id)
  ) ;

制約に名前をつける

  CONSTRAINT <制約の名前> <制約>

制約の削除

ALTER TABLE <テーブル名> DROP CONSTRAINT <制約の名前> ;

デフォルト値を設定

CREATE TABLE product
 (
    product_id  INT NOT NULL PRIMARY KEY ,
    name        CHAR(16) UNIQUE,
    price       INT DEFAULT 2000
 ) ;
192
205
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
192
205

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?