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)引用的列起码要有一个唯一约束

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。