oracle11g for 007 sql 第10章 including constraints

atx2418

oracle11g for 007 sql 第10章 including constraints

constraints是基于表的一切规则，规则主要是维护表的完整性。

约束条件分为5类分别是： 1.NOT NULL     ---非空
            2.UNIQUE   ----唯一性
            3.PRIMARY KEY ----主键约束
            4.FOREIGN KEY ----外键约束
            5.check    ----检查

1.NOT NULL and CHECK 是实现栏位一级完整性的，栏位可以为空。

2.UNIQUE and PRIMARY KEY 是行的约束。

3.FOREIGN KEY 是外键约束，是参照与被参照的关系。

定义约束条件的原则是允许给这个条件命名，如果不给它命名的话oracle会自动给它从系统中给它命名，
如果是系统命名的话是以sys_Cn命名的。约束条件是基于栏位一级和行一级，栏位一级主要针对column，
另外针对某几个column而言的情况，如：某个表里有姓和名，姓可定是唯一的，但名可以不唯一，这样
组合起来就是栏位唯一约束条件（称为表的唯一约束），约束条件是属于表的一部分，如果表的内容改
变那么约束条件会针对它进行校验。

Ⅰ.首先我们来创建一张表

例子：

SQL> create table test1
  2  (id int not null,               ----id 为非空
  3  lname varchar(20),
  4  fname varchar(20),
  5  constraint uk_test1_1 unique (lname,fname));  ----lname and fname是唯一的
 
Table created

我们来插入一条语句试试：

SQL> insert into test1 (lname,fname) values ( 'zhang' ,'hong');
 
insert into test1 (lname,fname) values ( 'zhang' ,'hong')
 
ORA-01400: cannot insert NULL into ("HR"."TEST1"."ID")

以上的ORA-01400说的是ID 不能为空，以上违法了非空的约束。

我们把ID插入一个值试试：

SQL> insert into test1 (id,lname,fname) values ( 1,'zhang','hong');
 
1 row inserted

这次成功了，违法约束条件。

我们在试试在插入相同的（lname，fname）的值试试：

SQL> insert into test1 (id,lname,fname) values ( 2,'zhang','hong');
 
insert into test1 (id,lname,fname) values ( 2,'zhang','hong')
 
ORA-00001: unique constraint (HR.UK_TEST1_1) violated

以上报的错误是说违法了唯一约束。

Ⅱ.我们也可以通过alter来添加约束

SQL> alter table test1 add constraint pk_test primary key (id);
 
Table altered
 
SQL> select owner,constraint_name,constraint_type,table_name,search_condition,generated from user_constraints i where i.TABLE_NAME='TEST1';
 
OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME        SEARCH_CONDITION      GENERATED
------------------------------ ------------------------------ --------------- -------------    -----------------      ------------------- 
HR                             SYS_C005424                    C               TEST1             "ID" IS NOT NULL      GENERATED NAME
HR                             UK_TEST1_1                     U               TEST1                                   USER NAME
HR                             PK_TEST                        P               TEST1                                   USER NAME

我们接着创建一个子表：

SQL> create table test2 ( rid int, name varchar(20),
  2  constraint fk_test2_2 foreign key(rid) references test1(id));
 
Table created

一旦外键和主键建立了约束以后，外键只能创建已有的值。

我先查看父表的主键值是多少：

SQL> select * from test1;
 
                                     ID LNAME                FNAME
--------------------------------------- -------------------- --------------------
                                      1 zhang                hong
                                     
我们在子表中插入值试试行不行：

 
SQL> insert into test2 values(2,'zhang');
 
insert into test2 values(2,'zhang')
 
ORA-02291: integrity constraint (HR.FK_TEST2_2) violated - parent key not found

以上提示说主键没有找到这个KEY的值，违法了约束条件。

注意：外键只能创建主键已有的值。

那能不能删除父表的数据呢？？

SQL> insert into test2 values(1,'china');                    ----先给子表插入数据
 
1 row inserted
 
SQL> delete from test1;
 
delete from test1
 
ORA-02292: integrity constraint (HR.FK_TEST2_2) violated - child record found                                    
 

以上的ORA-02292报错:违反完整约束条件，在子表中存在数据所以删除不了。

Ⅲ. check 的用法

check 是不可以使用子查询也不可以使用伪列：包括(CURRVAL,NEXTVAL,LEVEL,ROWNUM)

例子：

SQL> alter table test2
  2  add constraint ck_test2_2 check ( name is not null);
 
Table altered

SQL> insert into test2 values( 1,null);
 
insert into test2 values( 1,null)
 
ORA-02290: check constraint (HR.CK_TEST2_2) violated

在表结构改变下，可能会更改约束条件。

Ⅳ.删除一个约束条件。

SQL> alter table test1 drop constraint PK_TEST;
 
Table altered

如果在删除主键的时候有外键参照它就要加上CASCADE这个参数。

SQL> alter table test1 drop constraint SYS_C005424 cascade;
 
Table altered

如果从别的表导入到具有约束的条件的表中要把约束条件disabled。

SQL> alter table test2 disable constraint CK_TEST2_2;
 
Table altered

 

Ⅵ.查看约束条的属性

我们可以通过user_constraints视图查看到约束条件。

 

SQL> select * from user_constraints i where i.table_name='TEST2';

 

user_cons_columns 是查看栏位的约束相关的信息。

 

SQL> select * from user_cons_columns i where i.table_name='TEST2';