10、Oracle中的约 束constraint
最近项目要用到Oracle，奈何之前没有使用过，所以在B站上面找了一个学习视频，用于记录学习过程以及自己的思考。
视频链接：
【尚硅谷】Oracle数据库全套教程，oracle从安装到实战应用
如果有侵权，请联系删除，谢谢。
学习目标：
-
描述约束
-
创建和维护约束
1、什么是约束
约束是表级的强制规定
有以下五种约束:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
注意事项
-
如果不指定约束名 ，Oracle server 自动按照 SYS_Cn 的格式指定约束名
-
创建和修改约束:
- 建表的同时
- 建表之后
-
可以在表级或列级定义约束
-
可以通过数据字典视图查看约束
1.1、表级约束和列级约束
作用范围：
①列级约束只能作用在一个列上
②表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）
定义方式： 列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。
非空(not null) 约束只能定义在列上
2、定义约束
格式：
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
示例：
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
列级
column [CONSTRAINT constraint_name] constraint_type,
表级
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
3、NOT NULL 约束
保证列值不能为空:
只能定义在列级:
DBeaver 通过下面方式查看：选中查看的表 -> 属性 -> 约束 即可查看。
4、UNIQUE 约束
唯一约束，允许出现多个空值：NULL。
可以定义在表级或列级:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) UNIQUE, -- 系统命名
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email)); -- 用户自定义命名
可以声明在email后面：CONSTRAINT emp_email_uk UNIQUE,也可以如此末处声明。
5、PRIMARY KEY 约束
可以定义在表级或列级:
CREATE TABLE departments(
department_id NUMBER(4),
department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id)); -- 定义主键
6、FOREIGN KEY 约束
可以定义在表级或列级:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id), -- 定义外键
CONSTRAINT emp_email_uk UNIQUE(email));
6.1、FOREIGN KEY 约束的关键字
- FOREIGN KEY: 在表级指定子表中的列
- REFERENCES: 标示在父表中的列
- ON DELETE CASCADE: 级联删除。当父表中的列被删除时，子表中相对应的列也被删除
- ON DELETE SET NULL: 级联置空。子表中相应的列置空
create table emp (
id number(10) primary key,
name varchar2(26) unique,
sal number(8, 2),
dept_id number(4),
constraint dept_fk foreign key(dept_id) references dept(dept_id) on delete cascade
);
7、CHECK 约束
定义每一行必须满足的条件
..., salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0),...
示例：
create table emp (
id number(10) primary key,
name varchar2(26) unique,
sal number(8, 2) check(sal > 0 and sal < 1000000),
dept_id number(4),
constraint dept_fk foreign key(dept_id) references dept(dept_id) on delete set null
);
8、添加约束的语法
使用 ALTER TABLE 语句:
- 添加或删除约束,但是不能修改约束
- 有效化或无效化约束
添加 NOT NULL 约束要使用 MODIFY 语句
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
以create table emp as select * from employees;为例，添加和删除约束
alter table emp modify(empname varchar2(50) not null);
8.1、添加约束
添加约束举例
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
Table altered.
8.2、删除约束
从表 EMPLOYEES 中删除约束
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
Table altered.
8.3、无效化约束
在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk;
Table altered.
8.4、激活约束
- ENABLE 子句可将当前无效的约束激活
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
Table altered.
当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引
8.5、查询约束
查询数据字典视图 USER_CONSTRAINTS
SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
结果：
CONSTRAINT_NAME |CONSTRAINT_TYPE|SEARCH_CONDITION |
----------------+---------------+-----------------------+
EMP_LAST_NAME_NN|C |"LAST_NAME" IS NOT NULL|
EMP_EMAIL_NN |C |"EMAIL" IS NOT NULL |
EMP_HIRE_DATE_NN|C |"HIRE_DATE" IS NOT NULL|
EMP_JOB_NN |C |"JOB_ID" IS NOT NULL |
EMP_SALARY_MIN |C |salary > 0 |
EMP_EMAIL_UK |U | |
EMP_EMP_ID_PK |P | |
EMP_DEPT_FK |R | |
EMP_JOB_FK |R | |
EMP_MANAGER_FK |R | |
8.6、查询定义约束的列
查询数据字典视图 USER_CONS_COLUMNS
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
结果：
CONSTRAINT_NAME |COLUMN_NAME |
----------------+-------------+
EMP_LAST_NAME_NN|LAST_NAME |
EMP_EMAIL_NN |EMAIL |
EMP_HIRE_DATE_NN|HIRE_DATE |
EMP_JOB_NN |JOB_ID |
EMP_SALARY_MIN |SALARY |
EMP_EMAIL_UK |EMAIL |
EMP_EMP_ID_PK |EMPLOYEE_ID |
EMP_DEPT_FK |DEPARTMENT_ID|
EMP_JOB_FK |JOB_ID |
EMP_MANAGER_FK |MANAGER_ID |
课后笔记：
1.如何定义约束---在创建表的同时，添加对应属性的约束
1.1 表级约束 & 列级约束
create table emp1(
employee_id number(8),
salary number(8),
--列级约束
hire_date date not null,
dept_id number(8),
email varchar2(8) constraint emp1_email_uk unique,
name varchar2(8) constaint emp1_name_uu not null,
first_name varchar2(8),
--表级约束
constraint emp1_emp_id_pk primary key(employee_id),
constraint emp1_fir_name_uk unique(first_name),
constraint emp1_dept_id_fk foreign key(dept_id) references departments(department_id) ON DELETE CASCADE
)
1.2 只有not null 只能使用列级约束。其他的约束两种方式皆可
2.添加和删除表的约束--在创建表以后，只能添加和删除，不能修改
2.1添加
alter table emp1
add constaint emp1_sal_ck check(salary > 0)
2.1.1对于not null来讲，不用add，需要使用modify:
alter table emp1
modify (salary not null)
2.2 删除
alter table emp1
drop constaint emp1_sal_ck
2.3使某一个约束失效:此约束还存在于表中，只是不起作用
alter table emp1
disable constraint emp1_email_uk;
2.4使某一个约束激活：激活以后，此约束具有约束力
alter table emp1
enable constraint emp1_email_uk;
