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博客

oracle约束

最新文章