Oracle11g中的5个约束【主键约束、外键约束、唯一约束、检查约束、非空约束】

心已赠人 2022-10-24 13:10 189阅读 0赞

# **1、主键约束** #

![20210129161632319.png][]

使用primay key(字段名)即可完成主键约束

## a、eg:创建时添加主键约束 ##

create table IDS_FILE
    (
      FILE_LIST_ID VARCHAR2(20),
      CONTENT      BLOB,
      FILE_NAME    VARCHAR2(256),
      primary key(FILE_LIST_ID);
    )

## b、eg:使用Alter Table语句为表添加主键约束 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70][]

alter table IDS_FILE
      add constraint PK_IDS_FILE primary key (FILE_LIST_ID)

## c、移除主键约束 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 1][]

# 2、外键约束 #

![2021012916254431.png][]![20210129162550111.png][]

## 1、创建外键约束 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 2][]

## 2、修改表新增外键约束 ##

![20210129163458167.png][]

## 3、移除外键约束 ##

![20210129163512593.png][]

# 3、检查约束【CHECK约束】 #

![20210129163705198.png][]

## 1、创建表时添加CHECK约束 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 3][]

## 2、在修改数据表时添加CHECK约束 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 4][]

## 3、移除检查约束 ##

![20210129164028439.png][]

# 4、唯一约束【UNIQUE约束】 #

![20210129164136660.png][]

## 1、创建表时添加UNIQUE约束 ##

![20210129164946598.png][]

![20210129164951209.png][]

## 2、修改表时添加UNIQUE约束 ##

![2021012916502670.png][]

## 3、移除唯一约束 ##

![20210129165047626.png][]

# 5、非空约束【NOT NULL 约束】 #

![20210129165222224.png][]

![20210129165230559.png][]

## 1、创建非空约束 ##

create table IDS_FILE
    (
      FILE_LIST_ID VARCHAR2(20) not null,
      CONTENT      BLOB,
      FILE_NAME    VARCHAR2(256)
    )

## 2、修改表时设置非空约束 ##

![20210129165401191.png][]

## 3、修改非空约束为NULL即可取消非空约束 ##

![20210129165435562.png][]

[20210129161632319.png]: /images/20221024/ce8537ee25784861b331e3cac2351a1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70]: /images/20221024/2f97bff3255d4d6e89fb6aa6fcce6de0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 1]: /images/20221024/f39af01892b0414b96c6af2368ffd58e.png
[2021012916254431.png]: https://img-blog.csdnimg.cn/2021012916254431.png
[20210129162550111.png]: /images/20221024/b5c5b10439eb4e099f64124f7a05cda9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 2]: /images/20221024/78cfdd8575d24220a318fb2ee03f8934.png
[20210129163458167.png]: /images/20221024/4dd4fa7228f040ad9244a487e965e3e7.png
[20210129163512593.png]: /images/20221024/150d690d6ee24c85961deb4342c805ee.png
[20210129163705198.png]: /images/20221024/f1bb8a29a08441539b9a017eac1c22c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 3]: /images/20221024/1ccc5bdffaa14d29919f26f7f3c1bd90.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMzU3MjUyNTM5_size_16_color_FFFFFF_t_70 4]: /images/20221024/939792ade18b4d81823504fd31acc0e0.png
[20210129164028439.png]: /images/20221024/35b9c98993da47818e016fbdd77df28f.png
[20210129164136660.png]: /images/20221024/5c0d56b1e4704358a9b1bdafd0ec1ee0.png
[20210129164946598.png]: /images/20221024/5dbeda1a53874e9bac959677b3f052e3.png
[20210129164951209.png]: /images/20221024/00ba7bf252ec45e9afb82aa9c6d897b0.png
[2021012916502670.png]: /images/20221024/0716ab6df73740088ce587b0424b1991.png
[20210129165047626.png]: /images/20221024/0e53a83f381245dc801cfd294516d547.png
[20210129165222224.png]: /images/20221024/789887fe10264b8d81eb4cf770755416.png
[20210129165230559.png]: /images/20221024/3c544361c2bc47bdadf184b5c1629cb4.png
[20210129165401191.png]: /images/20221024/56b943049bc44458a830eef69e043d98.png
[20210129165435562.png]: /images/20221024/f94c77d0a548413fa42eecd659253e64.png