约束

约束

数据库中的约束类型

• 非空约束(NOTNULL)

• 唯一性约束(UNIQUE)

• 主键约束(PRIMARYKEY)

• 外键约束(FOREIGNKEY)

• 用户自定义约束(CHECK)

[外链图片转存失败(img-HGQLcLvp-1564747347856)(C:\Users\ad\AppData\Roaming\Typora\typora-user-images\1564733936764.png)]

非空约束(NOTNULL)

create table dept80(id number, name varchar2(20) not null, salary number(8,2) constraint dept_nn not null);

alter table dept80 modify location_id not null;

唯一性约束(UNIQUE)

create table dept80(id number, name varchar2(20) not null, salary number(8,2), constraint dept_unique UNIQUE(id));

alter table dept80 modify(name unique)

删除

alter table dept80 drop constraint DEPT_UNIQUE;

主键约束(PRIMARYKEY)

create table dept80(id number, name varchar2(20) not null, salary number(8,2), constraint dept_unique PRIMARY KEY(id));

create table dept80(id number, name varchar2(20) not null, salary number(8,2), constraint dept_unique PRIMARY KEY(id, name));

alter table dept80 modify(id PRIMARY KEY)

外键约束(FOREIGNKEY)

create table dept40(id number, d_id number, constraint dept_40_fk foreign key (d_id) references departments(department_id));

alter table dept40 add constraint dept_40_fk foreign key (d_id) references departments(department_id));

用户自定义约束(CHECK)

create table dept30 (id number, salary number(8, 2) constraint dep30_ck check(salary > 0));

alter table dept50 add constraint dept50_ck check(salary) > 1000

查看约束

select constraint_name, constraint_type, search_condition from user_constraints where table_name = 'DEPT';

禁用约束

alter table employees
disable constraint emp_emp_id_pk cascade

启用约束

alter table employees
enable constraint emp_emp_id_pk;

e constraint emp_emp_id_pk cascade

## 启用约束

alter table employees
enable constraint emp_emp_id_pk;