ITPub博客

首页 > 数据库 > Oracle > oracle约束

oracle约束

原创 Oracle 作者:lllllcheng 时间:2015-12-18 00:17:28 0 删除 编辑
oralce中使用约束是为了维护数据的完整性
not null(非空)
如果在列上定义了非空约束,那么当插于数据时,必须为列提供数据。
unique(唯一)
当定义了唯一约束后,该列值是不能重复的,但是可以为空
primary  key(主键)
用于唯一的标识行的数据,当定义主键约束后,该列不但不能重复而且不能为null。一张表最多只能有一个主键,但是可以由多个unique约束。
foreign key(外键)
用于定义主表和从表之间的关系,外键约束要定义在从表上,主表侧必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null;
check
用于强制行数据必须满足的条件,假定在id列上定义了check约束,并要求id列支在100~200之间,如果不在100~200之间就会提示出错。
下面我们通过具体的实验来加深理解

not null(非空):
如果在列上定义了非空约束,那么当插于数据时,必须为列提供数据。
......

unique约束(唯一)
该列值是不能重复的,但是可以为空


SQL> create table cheng1
  2  (name varchar(5),id number); 

Table created.

SQL> alter table cheng1 add constraint ccc unique(name);

Table altered.

SQL> insert into cheng1 values ('nameg',1);

1 row created.

SQL> /
insert into cheng1 values ('nameg',1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.CCC) violated

SQL> insert into cheng1 values (null,1);

1 row created.

SQL> select * from cheng1;


NAME          ID
----- ----------
nameg          1
               1
SQL> alter table cheng1 drop constraint ccc;

Table altered.

SQL> insert into cheng1 values ('nameg',1);

1 row created.


SQL> select * from cheng1;

NAME          ID
----- ----------
nameg          1
                   1
nameg          1

primary  key(主键)
当定义主键约束后,该列不但不能重复而且不能为null一张表最多只能有一个主键,但是可以由多个unique约束。

SQL> alter table cheng1 add constraint ccc primary key(name);
alter table cheng1 add constraint ccc primary key(name)
                                                  *
ERROR at line 1:
ORA-01449: column contains NULL values; cannot alter to NOT NULL

SQL> delete from cheng1 where id=1; 

3 rows deleted.

SQL> alter table cheng1 add constraint ccc primary key(name);

Table altered.

SQL> alter table cheng1 add constraint ccc primary key(id);
alter table cheng1 add constraint ccc primary key(id)
                                      *
ERROR at line 1:
ORA-02260: table can have only one primary key

SQL> insert into cheng1 values ('nnnn',1);

1 row created.

SQL> /
insert into cheng1 values ('nnnn',1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.CCC) violated

SQL> insert into cheng1 values (null,1);
insert into cheng1 values (null,1)
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."CHENG1"."NAME")

SQL> insert into cheng1 values ('cccc',1);

1 row created.

SQL> select * from cheng1;

NAME          ID
----- ----------
nnnn           1
cccc           1

foreign key(外键)用于定义主表和从表之间的关系,外键约束要定义在从表上,主表侧必须具有主键约束或是unique约束,定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null;

SQL> create table fk_cheng1 (name varchar(5),id number);

Table created.

SQL> alter table fk_cheng1 add constraint bbb foreign key(name) references cheng1(name);

Table altered.

SQL> select * from cheng1;


NAME          ID
----- ----------
nnnn           1
cccc           1

SQL> insert into fk_cheng1 values('aaaa',1);
insert into fk_cheng1 values('aaaa',1)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.BBB) violated - parent key not found

SQL> insert into fk_cheng1 values(null,1);

1 row created.

SQL>  insert into fk_cheng1 values('nnnn',1);

1 row created.

SQL> insert into fk_cheng1 values('cccc',1);


1 row created.

SQL> select * from fk_cheng1;

NAME          ID
----- ----------
                  1
nnnn           1
cccc           1

check:用于强制行数据必须满足的条件,假定在id列上定义了check约束,并要求id列支在100~200之间,如果不在100~200之间就会提示出错。

SQL> select * from cheng1;

NAME          ID
----- ----------
nnnn           1
cccc           1

SQL> alter table cheng1 add constraint aaa check(id between 1 and 30);

Table altered.

SQL> insert into cheng1 values ('aaaa',0);
insert into cheng1 values ('aaaa',0)
*
ERROR at line 1:
ORA-02290: check constraint (SYS.AAA) violated

SQL> insert into cheng1 values ('aaaa',32);
insert into cheng1 values ('aaaa',32)
*
ERROR at line 1:
ORA-02290: check constraint (SYS.AAA) violated


SQL> insert into cheng1 values (‘aaaa’,30); ('aaaa',30);


1 row created.

SQL> insert into cheng1 values ('aaaa',28);
insert into cheng1 values ('aaaa',28)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.CCC) violated

SQL> insert into cheng1 values ('eeee',28);

1 row created.

SQL> select * from cheng1;

NAME          ID
----- ----------
eeee          28
nnnn           1
cccc           1
aaaa          30

删除约束
可以通过数据字典视图user_constraints 来查询当前用户所拥有的约束信息;

SQL>  select constraint_name,constraint_type,status,validated from user_constraints where table_name='CHENG1';

CONSTRAINT_NAME                C STATUS   VALIDATED
------------------------------ - -------- -------------
CCC                            P ENABLED  VALIDATED
AAA                            C ENABLED  VALIDATED

SQL> alter table cheng1 drop primary key;
alter table cheng1 drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

SQL> alter table cheng1 drop primary key cascade;

Table altered.

SQL> select constraint_name,constraint_type,status,validated from user_constraints where table_name='FK_CHENG1';

no rows selected

通过查询数据字典视图user_cons_column,可以显示约束所对应的表列信息


SQL> col COLUMN_NAME for a10;
SQL> select owner,CONSTRAINT_NAME,TABLE_NAME,column_name from user_cons_columns where constraint_name='AAA';

OWNER      CONSTRAINT TABLE_NAME COLUMN_NAM
---------- ---------- ---------- ----------
SYS        AAA        CHENG1     ID

SQL> alter table cheng1 drop  constraint AAA;

Table altered.


 





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30606744/viewspace-1874287/,如需转载,请注明出处,否则将追究法律责任。

上一篇: linux下配置IP地址
请登录后发表评论 登录
全部评论

注册时间:2015-12-02

  • 博文量
    41
  • 访问量
    121416