一 约束的定义
约束是强加在表上的规则或条件.确保数据库满足业务规则.保证数据的完整性.当对表进行 DML 或 DDL 操作时,如果此操作会造成表中的数据违反约束条件或规则的话,系统就会拒绝执行这个操作.约束可以是列一级别的 也可以是表级别的.定义约束时没有给出约束的名字,ORACE 系统将为该约束自动生成一个名字,其格式为 SYS_Cn,其中 n 为自然数 (强烈建议各位在创建表或增加约束时,给约束定义名称.)
在 Oracle 中,数据完整性可以使用约束,触发器,应用程序(过程,函数)三种方法来实现,在这三种方法中,因为约束易于维护,并且具有最好的性能,所以作为维护数据完整性的首选.
列级约束:
列级定义是在定义列的同时定义约束;
column [CONSTRAINT constraint_name] constraint_type
表级约束:
表级定义是指在定义了所有列后,再定义约束,这里需要注意,not null 约束只能在列级上定义;
二 约束功能
column ,...,
[CONSTRAINT constraint_name] constraint_type (column,...)
约束的功能:实现一些业务规则,防止无效的垃圾数据进入数据库,维护数据库的完整性 (完整性指正确性与一致性).从而使数据库的开发和维护都更加容易.
三 约束的分类
1 not null(非空)
如果在列上定义了 not null,那么当插入数据时,必须为列提供,数据不能为 NULL.约束只能在列级定义,不能在表级定义.
2 unique(唯一)
当定义了唯一约束后,该列值是不能重复的,但是可以为 null.
3 primary key(主键)
用于唯一的标识表行的数据,当定义主键约束后,该列不但不能重复而且不能为 NULL.一张表最多只能有一个主键,但是可以由多个 unique 约束.
创建主键或唯一约束后,ORACLE 会自动创建一个与约束同名的索引(UNIQUENES 为 UNIQUE 唯一索引).需要注意的是:每个表只能有且有一个主键约束.
4 foreign key(外键)
用于定义主表和从表之间的关系,外键约束要定义在从表上,主要则必须具有主键约束或是 unique 约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为 NULL.
用来维护从表(Child Table)和主表(Parent Table)之间的引用完整性. 外键约束是个有争议性的约束,它一方面能够维护数据库的数据一致性,数据的完整性.防止错误的垃圾数据入库; 另外一方面它会增加表插入,更新等 SQL 性能的额外开销,不少系统里面通过业务逻辑控制来取消外键约束.例如在数据仓库中,就推荐禁用外键约束.
5 check
用于强制行数据必须满足的条件,假定在 sal 列上定义了 check 约束,并要求 sal 列值在 1000~2000 之间,如果不在 1000~2000 之间就会提示出错.
四 约束命令规范
约束名称建议自己定义一套命名规则,否则使用系统生成的约束名,很难能把它和对应的表,字段联系起来.
非空约束 NN 表名列名
唯一约束 UK 表名列名
主键约束 PK 表名
外键约束 FK 表名列名
条件约束 CK 表名列名
默认约束 DF 表名列名
如果约束名称超过 32 位长度,建议应该缩写表名,而不应用 NN 表名数字.不过具体视情况而定,很多时候 DF 表名_列名 这样命名,往往超出了 32 字符.所以有时候需要缩写表面或是采用其它规则.
五 创建约束
1 not null(非空)
创建
修改
SQL> create table t1(id number,name varchar2(20) constraint nn_t1_id not null);
Table created.
SQL> col CONSTRAINT_NAME format A50
SQL> col OWNER format A20
SQL> select constraint_name,constraint_type,owner from user_constraints;
CONSTRAINT_NAME C OWNER
SYS_C009047 V SCOTT
FK_DEPTNO R SCOTT
SYS_C009046 C SCOTT
NN_T1_ID C SCOTT
PK_DEPT P SCOTT
PK_EMP P SCOTT
6 rows selected.
2 unique(唯一)
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1(id number,name varchar2(20));
Table created.
SQL> alter table t1 modify id constraint nn_t1_id not null;
Table altered.
SQL> select constraint_name,constraint_type,owner from user_constraints;
CONSTRAINT_NAME C OWNER
FK_DEPTNO R SCOTT
SYS_C009046 C SCOTT
NN_T1_ID C SCOTT
PK_DEPT P SCOTT
PK_EMP P SCOTT
SYS_C009047 V SCOTT
创建
修改
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1(id number,qq number,constraint un_t1_qq unique(qq));
Table created.
3 primary key(主键)
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1(id number,qq number);
Table created.
SQL> alter table t1 add constraint un_t1_qq unique(qq);
Table altered.
创建
修改
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1(id number,qq number,constraint pk_t1_id primary key(id));
Table created.
4 foreign key(外键)
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1(id number,qq number);
Table created.
SQL> alter table t1 add constraint pk_t1_id primary key(id);
Table altered.
创建
修改
SQL > create table t2(id number, cc number, constraint fk_t2_id foreign key(id) references t1(id));
Table created.
当定义了外部键约束之后,要求外部键列的数据必须在主表的主键列(或惟一列)中存在,或者为 NULL,FOREING KEY 约束既可以在列级定义,也可以在表级定义.
SQL> drop table t2 purge
2 ;
Table dropped.
SQL> create table t2(id number,cc number);
Table created.
SQL> alter table t2 add constraint pk_t2_id foreign key(id) references t1(id);
Table altered.
关键字说明:
(1) FOREING KEY: 该选项用于指定在表级定义外部键约束.当在表级定义外部键约束时必须指定该选项,在列级定义外部键约束不需要指定该选项
(2) REFERENCES: 该选项用于指定主表名及其主键列.当定义外部键约束时,该选项必须指定..
(3) ON DELETE CASCAED: 该选项用于指定级联删除选项.如果在定义外部键约束时指定了该选项,那么当删除主表数据时会级联删除从表的相关数据.
(4) ON DELECT SET NULL: 该选项用于指定转换相关的外部键值为 NULL,如果在定义外部键约束时指定了该选项,那么当删除主表数据时会将从表外部键列的数据设置为 NULL.
5 check(检查性约束)
创建
修改
SQL > create table t3(id number, sal number, constraint ck_t3_sal check(sal between 5000 and 50000));
Table created.
六 维护约束
SQL> drop table t3 purge;
Table dropped.
SQL> create table t3(id number,sal number);
1 增加约束
(1) 如果增加 UNIQUE,PRIMARY KEY,FOREIGN KEY 和 CKECK 必须使用 ALTER TABLE 语句的 ADD 子句;
(2) 如果增加 NOT NULL 约束,那么必须使用 ALTER TABLE 语句的 MODIFY 子句,如:
2 修改约束名
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column,...)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;
在同一个方案中,约束名必须惟一, 并且约束名也不能与其他对象同名.当用 IMPDP 工具或者 IMP 工具导入其他对象时,如发现有同名的对象,将会出错
语法:
3 禁止约束
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
SQL> ALTER TABLE T1 rename constraint pk_t1_id to pk_t1_idnew
2 ;
Table altered.
禁止约束指使约束临时失效.当禁止了约束之后,约束规则将不再生效.在使用 SQL*LOADER 或 INSERT 装载数据之前,为了加快数据装载速度,应该首先禁止约束,然后装载数据.
语法:
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED 用于指定级联禁止从表的外部键
4 激活约束
SQL > ALTER TABLE T2 DISABLE constraint pk_t2_id;
Table altered.
语法:
5 删除约束
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
SQL > ALTER TABLE T2 enABLE constraint pk_t2_id;
Table altered.
当删除特定表的主键约束时,如果该表具有相关的从表,那么在删除主键约束时必须带有 CASCAED 选项
语法:
6 显示信息
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY
SQL> ALTER TABLE T3 drop constraint ck_t3_sal;
Table altered.
1.USER_CONSTRAINTS
2.USER_CONS_COLUMNS
3.SQL> set line 100
SQL> col CONSTRAINT_NAME format A20
SQL> col table_NAME format A20
SQL> col column_NAME format A20
SQL> select constraint_name,table_name,column_name from user_cons_columns where table_name='T1';
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
PK_T1_ID T1 ID
Table created.
SQL> alter table t3 add constraint ck_t3_sal check(sal>5000);
Table altered.
来源: http://www.linuxidc.com/Linux/2018-01/150062.htm