oracle约束控制

/**
	约束是 表级的强制规定
	有以下五种约束:
	NOT NULL 不为空
	UNIQUE	唯一 唯一和not null 不冲突  可以多个值为null 但是不能重复
	PRIMARY KEY 主键
	FOREIGN KEY 外键
	CHECK 检查条件
*/

/**
	注意事项:
  1.如果不指定约束名称 则默认按照SYS_Cn的格式指定约束名称
	2.创建和修改约束：
		- 建表的同事
		- 建表之后
	3.可以在表级或列级定义约束
	4.可以通过数据字典视图查看约束
*/

-- 创建列级约束
create table emp2(
	id number(10) constraint emp2_id_nn not null, -- constraint 约束（给约束起个名称）
	name varchar2(10) not null,-- 使用约束默认名称 SYS_Cn
	salary number(10,2)
)


select * from emp2;

--ORA-01400: cannot insert NULL into ("SCOTT"."EMP2"."NAME") 因为有了约束
INSERT INTO emp2 VALUES(1001,null,2000);


CREATE TABLE emp3(
	-- 列级约束
	id number(10) constraint emp3_id_uk UNIQUE, -- 唯一
	name VARCHAR2(10) constraint emp3_name_nn not null, --不为空
	email VARCHAR2(20),
	salary number(10,2),
	--表级约束
	constraint emp3_email_uk UNIQUE(email)
)


INSERT INTO emp3 values(1001,'aa','aa@126.com',2000)

-- unique constraint (SCOTT.EMP3_ID_UK) violated id重复 不唯一
INSERT INTO emp3 values(1001,'aa','aa1@126.com',2000)
--  ORA-00001: unique constraint (SCOTT.EMP3_EMAIL_UK) violated email 重复 不唯一
INSERT INTO emp3 values(1002,'aa','aa@126.com',2000)


CREATE TABLE emp4(
	-- 列级约束
	id number(10) constraint emp4_id_pk primary key, -- 唯一 不为空
	name VARCHAR2(10) constraint emp4_name_nn not null, --不为空
	email VARCHAR2(20),
	salary number(10,2),
	--表级约束
	constraint emp4_email_uk UNIQUE(email)
)

-- > ORA-01400: cannot insert NULL into ("SCOTT"."EMP4"."ID") 主键不能为空
INSERT INTO emp4 VALUES(null,'aa','aa@126.com',2000)
INSERT INTO emp4 VALUES(1001,'aa','aa@126.com',2000)

-- > ORA-00001: unique constraint (SCOTT.EMP4_ID_PK) violated  主键不能重复
INSERT INTO emp4 VALUES(1001,'bb','bb@126.com',2000)



CREATE TABLE emp5(
	-- 列级约束
	id number(10), -- 唯一 不为空
	name VARCHAR2(10) constraint emp5_name_nn not null, --不为空
	email VARCHAR2(20),
	salary number(10,2),
	--表级约束
	constraint emp5_email_uk UNIQUE(email),
	constraint emp5_id_pk primary key(id)
)



-- 外键 儿子 主键 爹  类似一个继承关系 儿子继承父亲List 但是 儿子不能改变这个List，只能从该List中选择一个元素
CREATE TABLE emp6(
	-- 列级约束
	id number(10), -- 唯一 不为空
	name VARCHAR2(10) constraint emp6_name_nn not null, --不为空
	email VARCHAR2(20),
	salary number(10,2),
	department_id number(10),
	--表级约束
	constraint emp6_email_uk UNIQUE(email),
	constraint emp6_id_pk primary key(id),
	--  foreign key (department_id)  该表的外键  是 department_id 关联 DEPARTMENTS 表的主键 DEPARTMENT_ID
	constraint emp6_dpmk_fk foreign key (department_id) references DEPARTMENTS(DEPARTMENT_ID)
)

-- 作用 插入数据时  外键与主键关联字段   如果主键 没有100字段 而外键插入100 就会违反约束
-- 简而言之 外键插入的数据 必须是主键拥有的值
-- 例 外键插入2000 而主键所对应表中没有 2000 字段 顾报错 ORA-02291  parent key not found

-- > ORA-02291: integrity constraint (SCOTT.EMP6_DPMK_FK) violated - parent key not found
INSERT INTO emp6 VALUES(1001,'aa','aa@123.com',2000,2000)


-- ON DELETE CASCADE (级联删除) 父亲的List没了  儿子的List也没了,列直接没了 儿子是继承自父亲的
-- ON DELETE SET NULL (级联置空) 父亲的List没了 儿子的List的值就为NULL,列还在值没了

-- 级联置空
CREATE TABLE emp7(
	-- 列级约束
	id number(10), -- 唯一 不为空
	name VARCHAR2(10) constraint emp7_name_nn not null, --不为空
	email VARCHAR2(20),
	salary number(10,2),
	department_id number(10),
	--表级约束
	constraint emp7_email_uk UNIQUE(email),
	constraint emp7_id_pk primary key(id),
	--  foreign key (department_id)  该表的外键  是 department_id 关联 DEPARTMENTS 表的主键 DEPARTMENT_ID
	constraint emp7_dpmk_fk foreign key (department_id) references DEPARTMENTS(DEPARTMENT_ID) on DELETE SET NULL 
)

-- check约束 相当于自定义约束
-- 工资不能低于500 不能高于30000

CREATE TABLE emp8(
	-- 列级约束
	id number(10), -- 唯一 不为空
	name VARCHAR2(10) constraint emp8_name_nn not null, --不为空
	email VARCHAR2(20),
	salary number(10,2) check(salary > 500 and salary < 30000),
	department_id number(10),
	--表级约束
	constraint emp8_email_uk UNIQUE(email),
	constraint emp8_id_pk primary key(id),
	--  foreign key (department_id)  该表的外键  是 department_id 关联 DEPARTMENTS 表的主键 DEPARTMENT_ID
	constraint emp8_dpmk_fk foreign key (department_id) references DEPARTMENTS(DEPARTMENT_ID) ON DELETE CASCADE
)

-- ORA-02290: check constraint (SCOTT.SYS_C0012098) violated   自定义约束工资低于500 了
INSERT INTO emp8 VALUES(1001,'aa','a@123.com',400,20)

/**
	建表之后：
	添加或修改约束
	1. 添加或删除约束，但是不能修改约束
	2. 有效化或无效化约束
	3. 添加 not null 约束要使用 modify语句
*/

-- 添加约束
ALTER TABLE emp5 
MODIFY (salary number(10,2) not null)

-- 删除约束
ALTER TABLE emp5
drop constraint EMP5_NAME_NN

ALTER TABLE emp5
DROP constraint SYS_C0012102

-- 添加约束

ALTER TABLE emp5 
ADD constraint emp5_name_uk UNIQUE(name)

-- 无效化约束

SELECT * from emp3
-- > ORA-00001: unique constraint (SCOTT.EMP3_EMAIL_UK) violated
INSERT INTO emp3 VALUES(1002,'bb','aa@126.com',2000)
-- 使SCOTT.EMP3_EMAIL_UK 唯一约束无效化

ALTER TABLE emp3
disable constraint EMP3_EMAIL_UK

-- SCOTT.EMP3_EMAIL_UK有效化
-- ORA-02299: cannot validate (SCOTT.EMP3_EMAIL_UK) - duplicate keys found 
-- 设置该列唯一约束 但是该列有重复的值
DELETE FROM EMP3 WHERE id = 1002 --删了之后就好了
ALTER TABLE emp3
enable constraint EMP3_EMAIL_UK



-- 练习

60. 约束需要注意的地方

	1). ** 非空约束只能定义在列级

	2). ** 唯一约束的列值可以为空

	3). ** 外键引用的列起码要有一个唯一约束	;