数据库约束

约束类型

• 非空约束 not null
• 唯一性约束 unique
• 主键约束 primary key
• 外键约束 foreign key
• 用户自定义约束 check
  可以给约束起名字，需要唯一

创建非空约束

• 创建表时创建
  定义约束名 constraint 约束名 not null
  如果不定义，系统将给出默认名称。

SQL> CREATE TABLE DEP(ID number not null, salary number constraint dep_nn not null);
Table created

SQL> desc dep;
Name   Type   Nullable Default Comments 
------ ------ -------- ------- -------- 
ID     NUMBER                           
SALARY NUMBER 

• 修改表时创建

SQL> alter table departs modify employee_id not null;
Table altered

创建唯一性约束

• 创建表时创建
  可使用 Constraint 定义约束名

SQL> CREATE TABLE DEP2(ID number, salary number, constraint dep2_salary_uk unique(salary));
Table created

SQL> CREATE TABLE DEP2(ID number unique, salary number constraint dep2_salary_uk unique);
Table created

• 修改表时创建

SQL> ALTER TABLE DEP2 ADD(MAIL VARCHAR2(20) UNIQUE);
Table altered

SQL> ALTER TABLE DEP2 ADD(EMAIL VARCHAR2(20) CONSTRAINT DEP2_EMAIL_UK UNIQUE);
Table altered

创建主键约束

不指定主键名，由数据库随机定义
SQL> create table pkey (id number primary key);
Table created
指定主键名
SQL> create table pkey (id number constraint pkey_id_pk primary key);
Table created

通过修改创建主键
SQL> create table pkey(id number);
Table created

SQL> alter table pkey modify(id constraint pkey_id_pk primary key);
Table altered

创建联合主键

SQL> create table pkey(id number, name varchar2(10), constraint pkey_id_name_pk primary key(id, name));
Table created

通过修改表创建联合主键
SQL> create table pkey(id number, name varchar2(10));
Table created
SQL> alter table pkey modify( constraint pkey_id_name_pk primary key(id, name));
Table altered
SQL> alter table pkey add constraint pkey_id_name_pk primary key(id, name);
Table altered

创建外键约束

SQL> create table pkey(id number, name varchar2(10), constraint peky_id_name_pk primary key(name));
Table created
SQL> create table fkey(id number, name varchar2(10), constraint fkey_id_fk foreign key(name) references pkey(name));
Table created

通过修改表创建外键

SQL> create table fkey(id number, name varchar2(10));
Table created

SQL> alter table fkey add constraint fkey_id_fk foreign key(name) references pkey(name);
Table altered

SQL> drop table fkey;
Table dropped

SQL> create table fkey(id number, name varchar2(10));
Table created

SQL> alter table fkey modify( constraint fkey_id_fk foreign key(name) references pkey(name));
Table altered

创建联合外键

SQL> create table pkey(id number, name varchar2(10), constraint peky_id_name_pk primary key(id, name));
Table created

SQL> create table fkey(id number, name varchar2(10), constraint fkey_id_fk foreign key(id, name) references pkey(id, name));
Table created

CHECK 创建用户自定义约束

创建表时创建check约束

SQL> create table ckey(id number, salary number(8,2), constraint ckey_salary check(salary > 0));
SQL> 
Table created

修改表时创建check约束

SQL> create table ckey(id number, salary number(8,2));
Table created

SQL> alter table ckey modify( constraint ckey_salary_ck check(salary>0));
Table altered

SQL> alter table ckey add constraint ckey_id_ck check(id > 1000);
Table altered

SQL> alter table ckey add (constraint ckey_id_ck check(id > 1000));
Table altered

禁用与启用约束

禁用约束

SQL> alter table ckey disable constraint ckey_id_ck;
Table altered

禁用主键约束时，如果有其它用作为外键引用，需要使用级联操作 cascade

SQL> alter table pkey disable constraint peky_id_name_pk;
alter table pkey disable constraint peky_id_name_pk
ORA-02297: 无法禁用约束条件 (HR.PEKY_ID_NAME_PK) - 存在相关性

SQL> alter table pkey disable constraint peky_id_name_pk cascade;
Table altered

启用约束

SQL> alter table ckey enable constraint ckey_id_ck;
Table altered