数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性是通过约束来实现的。在oracle中约束包括：not null , unique , primary key , foreign key , check 五种。
1.not null（非空）
如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
2.unique（唯一）
当定义了唯一约束之后，该列值是不能重复的，但是可以为null。
3.primary key（主键）
用于唯一的标志行的数据，当定义主键约束之后，该列不但不能重复而且不能为null。一张表最多只能有一个主键，但是可以有多个unique约束。
4.foreign key（外键）
用于定义主表和从表之间的关系，外键约束定义在从表上，主表则必须有主键约束或unique约束。当定义了外键约束后，要求外键列数据必须在主表的主键列存在或为null。
5.check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间，如果不在1000-2000之间就会提示出错。
约束定义语法：
约束表级定义是指在定义了所有列之后，在定义约束，这里需要注意的是，not null约束只能在列级上定义。
create table goods(
goodsid char(8),goodsname varchar2(50),category varchar(30),
constraint PK_goodsid primary key(goodsid));
约束列级定义是在定义列的同时定义约束。
create table goods(
goodsid char(8) constraint PK_goodsid primary key,
goodsname varchar2(50),
unitprice number(10,2) check(unitprice>0),
category varchar(30),
provider varchar(100));

如果在建表时忘记建立必要的约束，则可在建表后使用alter table命令为表增加约束，但是要注意的是，增加not null约束时，需要使用modify选项，而增加其他四种约束使用add选项。
(1)增加商品名不能为空
alter table goods modify goodsid not null;
(2)增加身份证不能重复
alter table customers add constraint uq_cardid unique(cardid);
(3)增加客户地址只能是a,b,c,d
alter table customers add constraint ck_address check(address in ('a','b','c','d'));

通过查看动态性能视图user_constraints可以显示当前用户所有的约束信息：
GOODS@orcl 02-SEP-14>desc user_constraints
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)

GOODS@orcl 02-SEP-14>col constraint_name for a20
GOODS@orcl 02-SEP-14>col table_name for a20

GOODS@orcl 02-SEP-14>select constraint_name,constraint_type,table_name
2 from user_constraints
3 where table_name in('GOODS','CUSTOMERS','PURCHASES');

CONSTRAINT_NAME C TABLE_NAME
-------------------- - --------------------
SYS_C0011122 C CUSTOMERS
SYS_C0011123 C CUSTOMERS
SYS_C0011124 P CUSTOMERS
SYS_C0011125 U CUSTOMERS
UQ_CARDID U CUSTOMERS
CK_ADDRESS C CUSTOMERS
SYS_C0011120 C GOODS
SYS_C0011121 P GOODS
SYS_C0011129 C GOODS
SYS_C0011126 C PURCHASES
SYS_C0011127 R PURCHASES

CONSTRAINT_NAME C TABLE_NAME
-------------------- - --------------------
FK001 R PURCHASES

可以通过user_cons_columns查看约束的列表信息：

GOODS@orcl 02-SEP-14>select column_name,constraint_name,table_name
2 from user_cons_columns
3 where constraint_name='CK_ADDRESS';

COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME TABLE_NAME
-------------------- --------------------
ADDRESS
CK_ADDRESS CUSTOMERS

如果不需要某个约束的话可以删除：
GOODS@orcl 02-SEP-14>alter table customers drop constraint ck_address;

Table altered.

在删除主键约束时，可能有如下错误：
GOODS@orcl 02-SEP-14>alter table goods drop primary key;
alter table goods drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

这是因为如果在两表存在主从关系，那么在删除主表主键约束的同时，必须加上cascade选项：
GOODS@orcl 02-SEP-14>alter table goods drop primary key cascade;

Table altered.

ITPub博客

oracle中的约束

最新文章