oracle 之 约束（第三章）

oracle 之 约束

   oracle的constraint有五种：

       1：检查约束

       2：非空约束

       3：唯一约束

       4：主键约束

       5：外键约束

         一）   下面通过操作说明约束的创建方法：

 

SQL> create table bookinfo(
  2  bookid number(13),   
  3  bookname varchar2(20),
  4  publish varchar2(30),
  5  pubdate date,
  6  price number(9,3),
  7  author varchar2(20),
  8  store number not null,
  9  reader number(20),
 10  remarks varchar2(25),
 11  constraint chk2_key check(price>10 and price<=200),
 12  constraint unique_key unique(bookname),
 13  constraint prima_key primary key (bookid),
 14  constraint foreign_key foreign key(publish) references PUBLISHINFO (PUBLISHID)
 15  );

Table created.

SQL> 

         二）查询该用户下创建的约束信息：

SQL> r
  1* select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='BOOKINFO'

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS                           SYS_C0011171                   C BOOKINFO
RHYS                           CHK2_KEY                       C BOOKINFO
RHYS                           PRIMA_KEY                      P BOOKINFO
RHYS                           UNIQUE_KEY                     U BOOKINFO
RHYS                           FOREIGN_KEY                    R BOOKINFO

SQL> 

在user_constraints视图中C列表示：C代表检查约束，P代表主键约束，U代表唯一索引约束，R代表外键约束

 通过user_cons_columns这个视图可以看到该用户下所有的每个约束对应的表列的名字关系情况。

   eg：

     

SQL> select * from user_cons_columns;
 
OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME                                                                        POSITION
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
RHYS                           BIN$2V5HaJApQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 BOOKNAME                                                                                  1
RHYS                           BIN$2V5HaJAoQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 BOOKID                                                                                    1
RHYS                           BIN$2V5HaJAnQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 PRICE                                                                            
RHYS                           BIN$2V5HaJAmQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 STORE                                                                            
RHYS                           FOREIGN_KEY                    BOOKINFO                       PUBLISH                                                                                   1
RHYS                           UNIQUE_KEY                     BOOKINFO                       BOOKNAME                                                                                  1
RHYS                           PRIMA_KEY                      BOOKINFO                       BOOKID                                                                                    1
RHYS                           BIN$2V5HaJAiQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 BOOKNAME                                                                                  1
RHYS                           BIN$2V5HaJASQoLgQyfgEwp/yg==$0 BIN$2V5HaJATQoLgQyfgEwp/yg==$0 STORE                                                                            
RHYS                           BIN$2V5HaJARQoLgQyfgEwp/yg==$0 BIN$2V5HaJATQoLgQyfgEwp/yg==$0 BOOKNAME                                                                         
RHYS                           BIN$2V5HaJAQQoLgQyfgEwp/yg==$0 BIN$2V5HaJATQoLgQyfgEwp/yg==$0 BOOKID                                                                           
RHYS                           BIN$2V5HaJAhQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 BOOKID                                                                                    1
RHYS                           BIN$2V5HaJAgQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 PRICE                                                                            
RHYS                           BIN$2V5HaJAfQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 STORE                                                                            
RHYS                           BIN$2V5HaJAOQoLgQyfgEwp/yg==$0 BIN$2V5HaJAPQoLgQyfgEwp/yg==$0 STORE                                                                            
RHYS                           PRI_KEY                        PUBLISHINFO                    PUBLISHID                                                                                 1
RHYS                           UNIQUE_CHK_KEY                 BOOKINFO_03                    BOOKID                                                                                    1
RHYS                           SYS_C0011147                   BOOKINFO_03                    BOOKID                                                                           
RHYS                           BIN$2V5HaJAeQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 PUBLISH                                                                          
RHYS                           SYS_C0011145                   BOOKINFO_02                    STORE                                                                            
 
OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME                                                                        POSITION
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
RHYS                           BIN$2V5HaJAdQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 BOOKID                                                                           
RHYS                           FK_DEPTNO                      EMP                            DEPTNO                                                                                    1
RHYS                           PK_DEPT                        DEPT                           DEPTNO                                                                                    1
RHYS                           SYS_C0011142                   TAIAN                          SUBJECT                                                                          
RHYS                           PK_EMP                         EMP                            EMPNO                                                                                     1
 
25 rows selected
 
SQL> 

注意：带BIN开头的约束名都是已经放入回收站的，可以不用管它，如果想清空的话，可以purge该用户的回收站。如删除当前用户下回收站信息：purge recyclebin;

       三）修改已创建的约束情况：

       1）添加约束命令格式

            

alter table table_name add constraint  primariy_name primary key (column_name);
alter table table_name add constraint foreign_name foreign key (column_name) references table_name(column_name;
alter table table_name add constraint unique_name unique(column);
alter table table_name add constraint check_name check(value);

alter table table_name modify column not null;

     2）删除表的约束条件

          

alter table table_name drop constraint constraint_name;

   eg:

        

SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='BOOKINFO';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS                           CHK2_KEY                       C BOOKINFO
RHYS                           PRIMA_KEY                      P BOOKINFO
RHYS                           UNIQUE_KEY                     U BOOKINFO
RHYS                           FOREIGN_KEY                    R BOOKINFO

SQL> alter table bookinfo drop constraint CHK2_KEY;

Table altered.

SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='BOOKINFO';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS                           PRIMA_KEY                      P BOOKINFO
RHYS                           UNIQUE_KEY                     U BOOKINFO
RHYS                           FOREIGN_KEY                    R BOOKINFO

SQL> 

 

    注意：如果想把表中某一列的非空约束去掉，那么可以使用：alter  table  table_name modify column_name NUll;

eg:

         

SQL> desc bookinfo;
 Name                                                                          Null?    Type
 ----------------------------------------------------------------------------- -------- ----------------------------------------------------
 BOOKID                                                                        NOT NULL NUMBER(13)
 BOOKNAME                                                                               VARCHAR2(20)
 PUBLISH                                                                                VARCHAR2(30)
 PUBDATE                                                                                DATE
 PRICE                                                                                  NUMBER(9,3)
 AUTHOR                                                                                 VARCHAR2(20)
 STORE                                                                         NOT NULL NUMBER
 READER                                                                                 NUMBER(20)
 REMARKS                                                                                VARCHAR2(25)

SQL> alter table bookinfo modify STORE NULL;

Table altered.

SQL> DESC BOOKINFO;
 Name                                                                          Null?    Type
 ----------------------------------------------------------------------------- -------- ----------------------------------------------------
 BOOKID                                                                        NOT NULL NUMBER(13)
 BOOKNAME                                                                               VARCHAR2(20)
 PUBLISH                                                                                VARCHAR2(30)
 PUBDATE                                                                                DATE
 PRICE                                                                                  NUMBER(9,3)
 AUTHOR                                                                                 VARCHAR2(20)
 STORE                                                                                  NUMBER
 READER                                                                                 NUMBER(20)
 REMARKS                                                                                VARCHAR2(25)

SQL> 

   注意：数据库没有修改约束这种情景。