oralce中使用约束是为了维护数据的完整性
not null(非空)
如果在列上定义了非空约束,那么当插于数据时,必须为列提供数据。
unique(唯一)
当定义了唯一约束后,该列值是不能重复的,但是可以为空
primary key(主键)
用于唯一的标识行的数据,当定义主键约束后,该列不但不能重复而且不能为null。一张表最多只能有一个主键,但是可以由多个unique约束。
foreign key(外键)
用于定义主表和从表之间的关系,外键约束要定义在从表上,主表侧必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null;
check
用于强制行数据必须满足的条件,假定在id列上定义了check约束,并要求id列支在100~200之间,如果不在100~200之间就会提示出错。
下面我们通过具体的实验来加深理解
not null(非空):如果在列上定义了非空约束,那么当插于数据时,必须为列提供数据。
......
unique约束(唯一): 该列值是不能重复的,但是可以为空
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
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.
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> 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之间就会提示出错。
2 (name varchar(5),id number);
Table created.
SQL> alter table cheng1 add constraint ccc unique(name);
Table altered.
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;
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
3 rows deleted.
SQL> alter table cheng1 add constraint ccc primary key(name);
Table altered.
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
insert into cheng1 values (null,1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."CHENG1"."NAME")
1 row created.
NAME ID
----- ----------
nnnn 1
cccc 1
SQL> create table fk_cheng1 (name varchar(5),id number);
Table created.
Table altered.
SQL> select * from cheng1;
NAME ID
----- ----------
nnnn 1
cccc 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.
1 row created.
SQL> insert into fk_cheng1 values ('cccc',1);
1 row created.
NAME ID
----- ----------
1
nnnn 1
cccc 1
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);NAME ID
----- ----------
nnnn 1
cccc 1
Table altered.
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.
insert into cheng1 values ('aaaa',28)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.CCC) violated
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
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.
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
Table altered.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30606744/viewspace-1874287/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30606744/viewspace-1874287/