SQL基础学习笔记（三）—约束

约束:

一、创建表时，定义约束

create table emp2(
                  id number(10) constraint emp2_id_nn not null,
                  name varchar2(20) not null,
                  salary number(10,2)
                  )
constraint emp2_id_nn  给约束起个名增加阅读性，不写也行，会自动加上sys_XXXX

作用范围：
①列级约束只能作用在一个列上

②表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）

定义方式：列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。

注意，非空(not null) 约束只能定义在列上

1.not null约束  列级约束

insert into emp2
values(1001,null,2000)

没有定义not null约束的可以为空，定义了not null不能为空

2.unique约束（唯一约束）  列级、表级约束

insert into emp2
values(1001,null,2000)

写几个，就会插入几个，但是ID只能有一个，所以，添加数据也要加一个约束，不能赋同样的值

create table emp3(
                  id number(10) constraint emp3_id_uk unique,
                  name varchar2(20) constraint emp3_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                  --表级约束
                   constraint emp3_email_uk unique(email)
                  )

只有unique约束，而没有notnull约束，是可以赋空值的，且空值，不看做重复内容的标准，也就是只有null可以是重复的，其他值都不可以重复

3.primary key (主键约束)    列级、表级约束
比如在数据库，员工表中查某个人的信息，人名有很多是重复，这就不好查，但每个人的身份证号是唯一的，主键的作用就类似个身份证号

create table emp4(
                  id number(10) constraint emp4_id_pk primary key,
                  name varchar2(20) constraint emp4_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                   constraint emp4_email_uk unique(email)
                  )

Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
ID     NUMBER(10)                             
NAME   VARCHAR2(20)                           
SALARY NUMBER(10,2) Y                         
EMAIL  VARCHAR2(20) Y   

主键要求 非空，唯一，相当于自动加上了not null，unique的限制

create table emp5(
                  id number(10) ,
                  name varchar2(20) constraint emp5_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                  --表级约束
                   constraint emp5_email_uk unique(email),
                   constraint emp5_id_pk primary key(id)
                  )

4.foreign key(外键)   列级、表级约束

类似两个朋友圈，有一个人属于A圈，也属于B圈，这个人从A圈连接着B圈，那这个人就是个外键
我和C不认识，但是通过B就可以和C认识，这个B就是外键

create table emp6(
                  id number(10) ,
                  name varchar2(20) constraint emp6_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                  department_id number(10),
                  --表级约束
                   constraint emp6_email_uk unique(email),
                   constraint emp6_id_pk primary key(id),
                   constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id)
                  )

插入数据时，department_id 必须在departments表中有，才能插入

注意:
ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除，也就是departments表中60号部门删除，emp6表中的60部门
的人也全部删除

ON DELETE SET NULL(级联置空): 子表中相应的列置空，也就是departments表中60号部门置空，emp6表中的原60部门的人的部门id为null，但人还在

--级联置空
create table emp7(
                id number(10) ,
                name varchar2(20) constraint emp7_name_nn not null,
                salary number(10,2),
                email varchar2(20),
                department_id number(10),
--表级约束
 constraint emp7_email_uk unique(email),
 constraint emp7_id_pk primary key(id),
 constraint emp7_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
                   )

5.check约束  列级、表级约束

给salary加check约束

create table emp8(
                id number(10) ,
                name varchar2(20) constraint emp8_name_nn not null,
                salary number(10,2)constraint emp8_salary check(salary > 1500 and salary < 20000),
                email varchar2(20),
                department_id number(10),
--表级约束
 constraint emp8_email_uk unique(email),
 constraint emp8_id_pk primary key(id),
 constraint emp8_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
                   )

限制salary在(1500,20000)


二、创建表后，增加约束
1.使用 ALTER TABLE 语句:
添加或删除约束,但是不能修改约束
2.有效化或无效化约束
3.添加 NOT NULL 约束要使用 MODIFY 语句

1.添加一个not null约束

alter table emp5
modify (salary number(11,2) not null)

2.删除

alter table emp5
drop constraint emp5_name_nn 

3.添加unique约束

alter table emp5
add constraint emp5_name_uk unique(name)

4.将约束无效化

emp3表有unique约束，使之无效化，但是并没有删除

SQL> alter table emp3
  2  disable constraint emp3_email_uk;

5.有效化，前提是，有效化前，该列的数据要符合被优化的约束的约束条件，比如unique约束，有效化这个约束，就必须保证这个列的数据不能重复

SQL> alter table emp3
  2  enable constraint emp3_email_uk;

6.查询约束

SELECT constraint_name, constraint_type,
search_condition
FROM 
user_constraints
WHERE 
table_name = 'EMPLOYEES';

7.查询定义约束的列

SELECT constraint_name, column_name
FROM
user_cons_columns
WHERE 
table_name = 'EMPLOYEES';

约束需要注意的地方

1). ** 非空约束(not null)只能定义在列级
2). ** 唯一约束(unique)的列值可以为空
3). ** 外键(foreign key)引用的列起码要有一个唯一约束