—–约束
约束是加在表上的一种强制性的规则
,是保证 数据完整性 的一种重要手段 .
当向表中插入或修改数据时,必须满足约束所规定的条件
.
如性别必须是”男/女”,部门号必须是已存在的部门号等等.
-详细点:
约束是强加在表上的规则或条件
。确保数据库满足业务规则。保证数据的完整性。
当对表进行DML或DDL操作时,如果此操作会造成表中的数据违反约束条件或规则的话,系统就会拒绝执行这个操作。
约束可以是列级别的 也可以是表级别的
。定义约束时没有给出约束的名字,ORACE系统将为该约束自动生成一个名字,其格式为SYS_Cn,其中n为自然数(强烈建议各位在创建表或增加约束时,给约束定义名称。)
一般而言,保证数据完整性大致有3种方法:
程序代码,触发器,约束.
约束类型:
NOT NULL 非空约束
UNIQUE 唯一性约束
PRIMARY KEY 主键约束
FOREIGN KEY 外键约束
CHECK 检查约束
–约束名称建议自己定义一套命名规则,否则使用系统生成的约束名,很难能把它和对应的表、字段联系起来。
非空约束 NN_表名_列名
唯一约束 UK_表名_列名
主键约束 PK_表名
外键约束 FK_表名_列名
条件约束 CK_表名_列名
默认约束 DF_表名_列名
如果约束名称超过32位长度,建议应该缩写表名,而不应用NN_表名数字。不过具体视情况而定,很多时候 DF表名_列名 这样命名,往往超出了32字符。所以有时候需要缩写表面或是采用其它规则。
–约束的创建1(列级别上定义约束)
create table 表名 (
列1 数据类型 [constraint 约束名1] 约束类型,
列2 数据类型 [constraint 约束名2] 约束类型,
…
);
-注意:假如没有"constraint 约束名",此时它的约束名称是系统创建的,一般为"SYS_CN".
-要点就是在列的结尾直接添加,如果同一列有多个约束,通过逗号来分隔.
–约束的创建2(表级别上定义约束)
create table 表名 (
列1 数据类型,
列2 数据类型,
…
constraint 约束名1 约束类型(列名),
constraint 约束名2 约束类型(列名),
…
);
注意: not null 只能写在列上来约束,它不是一个表级约束,而是一个列级约束。
其他4种都可以列声明之后单独写
如:
create table stu (
sno number,
sname varchar2(30) not null,
email varchar2(30),
gender char(2),
constraint pk primary key(sno),
constraint uemail unique(email),
constraint chkgen check (gender in('m','f'))
);
–外键的声明
外键的声明稍复杂一点,因为牵涉到另一张表
create table 表名 (
列1 列类型
contraint 约束名 foreign key (列名) references 其他表(列名)
)
注意: 另一张表被引用的列需是主键或Unique
补加:
–注释
COMMENT ON TABLE “DM”.”DEPT” IS ‘部门表’;
COMMENT ON COLUMN “DM”.”DEPT”.”DEPTNO” IS’部门编号’;
--如果表已经创建了,但是没有约束,后期再加约束,也是可以的。
为什么会有这种应用场景?
1:如果有大量数据要插入,或转移的时候,如1000万条数据批量插入,
如果存在约束,插入速度是比较慢的。
可以先不加任何约束,导入数据完毕后,再统一加上。
2:表之间存在外键约束时,比如有员工表,部门表, 员工的部门id—>部门表id
想批量员工,可能因为外键约束,引起问题。
可以先不加约束,待数据导入完毕,再统一加约束。
语法: alter table 表名 add (
Constraint 约束名 约束类型(列)..
);
如:
alter table student add (
constraint uk_sno unique(sno),
constraint gen_chk check(gender in ('男','女'))
);
–升级知识:
(1) 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type(类名);
(2) 如果增加NOT NULL约束,那么必须使用ALTER TABLE语句的MODIFY子句
ALTER TABLE table_name MODIFY (列名,列类型)
[CONSTRAINT constraint_name] NOT NULL;
–删除约束
Alter table 表名 drop constraint 约束名
–可选级联约束删除(一个表只有一个主键约束,可以直接删除。)
DROP PRIMARY KEY [CASCADE]
–修改约束名
约束名必须唯一,并且约束名也不能与其他对象同名。
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
-注意:主键无法更改约束名字
–禁用和启用约束
ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT constraint_name;
–修改约束类型
想改一个约束类型,只能先删除约束再添加新的约束
(使用ALTER TABLE语句的MODIFY子句.这样不行??)
附加:
—约束状态
很多时候由于业务需要,比如我们有大量的历史数据,需要和现有数据合并,当前表存在数据库约束(如非空约束),而这些历史数据又包含违背非空约束的数据行,为了避免导入时由于违反约束而导入失败,我们通过调整约束状态来达到目的。
数据库约束有两类状态
启用/禁用(enable/disable):是否对新变更的数据启用约束验证
验证/非验证 (validate/novalidate) :是否对表中已客观存在的数据进行约束验证
这两类四种状态从语法角度讲可以随意组合,默认是 enable validate
下面我们来看着四类组合会分别出现什么样的效果:
enable validate : 默认的约束组合状态,无法添加违反约束的数据行,数据表中也不能存在违反约束的数据行;
enable novalidate : 无法添加违反约束的数据行,但对已存在的违反约束的数据行不做验证;
disable validate : 可以添加违反约束的数据行,但对已存在的违反约束的数据行会做约束验证(从描述中可以看出来,这本来就是一种相互矛盾的约束组合,只不过是语法上支持这种组合罢了,造成的结果就是会导致DML失败)
disable novalidate : 可以添加违法约束的数据行,对已存在的违反约束的数据行也不做验证。
拿上面的例子来说,我们需要上传大量违反非空约束的历史数据(从业务角度讲这些数据不会造成系统功能异常),可以临时将约束状态转为 disable novalidate,以保证这些不合要求的数据导入表中
alter table emp modify constraint emp_ename_nn disable novalidate;
在数据导入完成之后,我们再将约束状态转为enable novalidate 以确保之后添加的数据不会再违反约束
alter table emp modify constraint emp_ename_nn enable novalidate;