Orcale_07约束

可以对字段进行限制，无效数据无法输入

一、约束类型

约束类型		缩写
not null	非空约束，添加该约束的字段，不可以为空	nn
unique	唯一约束，添加该约束的字段，不可可以出现重复	uq
primary key	主键约束，添加该约束一般no/id等字段，不可为空且不可重发	pk
foreign key	外键约束，必须依赖于主键村子，没有主键就没有外键（可重复为空）	fk
check	检查约束，之地那个列必须满足指定条件

约束也是数据库对象之一，为表做服务

1.约束名：

        ①使用系统定义名称 ：sys_cn  n表示当前用户下的第n个约束，不重复的

        ②自定义名称 ：表名_字段_约束类型

2.约束添加范式

• 建表及约束
• 建表后追加约束

二、约束

1.非空约束

1)语法: 

create table 表名(

        列名 类型（m）not null --系统命名 

)

create table 表名(

        列名 类型（m）constraint 约束名 not null --自定义名称

)

2)追加非空约束    如果表中有空字段，则无法追加

alter table 表名

modify 字段 not null;/modify 字段constraint 约束名 not null;

3)删除非空约束

alter table 表名

modify 字段null;

--约束 
--建表级约束
--1.非空约束
create table a(
       a_name varchar2(50) not null--系统命名
)
insert into  a(a_name) values(null);--不允许
insert into  a(a_name) values('tom');--一行已插入

select * from a;
-------------------------------------------------------------
create table aa(                     --表名_字段名——约束类型
       a_name varchar2(50) constraint aa_a_name_nn not null
)
insert into  a(a_name) values(null);--不允许
insert into  a(a_name) values('tom');--一行已插入

select * from aa; 

---------------------------------------------------------------
--建表后追加约束
create table aaa(
       a_name varchar2(50) 
)

insert into aaa values(null);

select * from aaa;

--追加
alter table aaa
modify a_name not null;--不能追加
--若原表中有空字段，就追加不了

delete from aaa;--删掉空字段后可以追加约束

--删除
alter table aaa
modify a_name null;--删除后又可以插入空值

2.唯一约束 

语法：

1）在列级添加唯一约束

create table 表名（

        列名 类型（m） unique   ---系统命名

）

2)在表级添加约束

create table 表名（

        列名 类型（m），       

constraint   约束名 unique(列名)  ---- 自定义名称

）

3)追加唯一约束

alter table 表名

add constraint 约束名 unique(列名);

4)删除唯一约束

alter table 表名

drop constraint 约束名；

--2.唯一约束
--列级添加约束
create table b(
       b_name varchar2(50) unique 
);
insert into b(b_name) values('tom');
insert into b(b_name) values('tom');--添加重复数据，违反唯一约束条件

select * from b;

--表级添加
create table bb(
       b_name varchar2(50),
       constraint bb_b_name_uq unique(b_name),
       b_gender varchar2(3)
)
insert into bb(b_name) values('tom');
insert into bb(b_name) values('tom');--添加重复数据，违反唯一约束条件

--追加唯一约束
create table bbb(
       b_name varchar2(50)
)

--追加
alter table bbb
add constraint bbb_b_name_uq unique(b_name);

insert into bbb(b_name) values('tom');
insert into bbb(b_name) values('tom');--添加重复数据，违反唯一约束条件

--删除
alter table bbb
drop constraint bbb_b_name_uq;

select * from bbb;

 3.主键约束

语法：

create table 表名（

        列名 类型（m） primary key   ---系统命名

）

1）在表级创建

create table 表名(

        列名 类型(m)，

        constraint 约束名 primarily key(列名) --自定义名称

)

2）追加主键约束

alter table 表名

add constraint 约束名 primary key(列名);

3）删除主键约束

alter table 表名

drop constraint  ccc_c_id_pk;

------------------------------------------------------------------------------
--主键约束
--在列级创建
create table c(
       c_id number(7) primary key 
)

insert into c(c_id) values(1);
insert into c(c_id) values(null);--主键约束无法加入空值
insert into c(c_id) values(1);--主键约束无法加入相同的值

select * from c;

--在表级创建
create table cc(
       c_id number(7),
       constraint cc_c_id-pk primary key (c_id),
       c_name varchar2(5)
)


insert into cc(c_id) values(1);
insert into cc(c_id) values(null);--主键约束无法加入空值
insert into cc(c_id) values(1);--主键约束无法加入相同的值

--追加主键约束
create table ccc(
       c_id number(7)
)

--追加
alter table ccc
add constraint ccc_c_id_pk primary key(c_id); 

insert into ccc(c_id) values(1);
insert into ccc(c_id) values(null);
insert into ccc(c_id) values(1);

select * from ccc


--删除
alter table ccc
drop constraint ccc_c_id_pk;

 4.外键约束

外键约束必须依赖主键存在

语法

1)在表级添加约束

create table 表名(

列名 类型(m),

constraint 约束名 foreign key(列名) references 主键表(主键字段)

)

references ：参考

主键表（父表）中没有的主键是无法使用的

可以向外键表中插入空值

可以向外键表中插入重复的值

2)追加外键约束

alter table 表名

add constraint 约束名 foreign key(列名) references 主键表(主键字段)

3)删除外键约束

alter table 表名

drop constraint 约束名；

4)级联操作

在主键列删除时，外键列的情况

①级联置空

on delete set null 在主键列删除时，外键列对应字段设置为空

②级联删除

on delete cascade 在主键列删除时，外键列对应的字段同时也被删除

--4.外键约束
--主键表
create table test (
       t_id number(7) primary key,
       t_name varchar2(50)
)

--如果一个字段在一张表中（表一）中是主关键字，在另一张表（表二）中不是主关键字，则这个字段为表二的外键
--外键的作用：保持数据的一致性，完整性，主要的目的是控制存储在外键表中的数据，使两张表形成关联
--外键表
create table d(
       d_id number(7) primary key,
       d_name varchar2(50),
       t_id number(7),
       constraint d_t_id_fk foreign key (t_id) references test(t_id)
);

--向外键表中添加数据
--①
insert into d(d_id,d_name,t_id) values(1001,'tom',1);
--逐渐表（父亲）中没有的主键是无法使用的

--向主键表中添加数据
insert into test values(1,'java');
insert into test values(2,'oracle');
select * from test;
select * from d;


--②
insert into d(d_id,d_name,t_id) values(1002,'jack',null);--可以向外键中插入空值

--③
insert into d(d_id,d_name,t_id) values(1003,'tom',1);--可以向外键中插入重复的值

--追加   主键列可以被多张表作为外键列
create table dd(
       d_id number(7) primary key,
       d_name varchar2(50),
       t_id number(7)
)

alter table dd
add constraint dd_t_id_fk foreign key(t_id) references test(t_id);

insert into dd(d_id,d_name,t_id) values(1001,'tom',3);
insert into dd(d_id,d_name,t_id) values(1002,'jack',null);
insert into dd(d_id,d_name,t_id) values(1003,'rose',1);

select * from dd;

--删除
alter table dd
drop constraint dd_t_id_fk;

insert into dd(d_id,d_name,t_id) values(1004,'rose',5);


---主键表 ，mydept 部门id 部门名字 添加3条记录
--外键表myemp 员工id 员工姓名  员工的薪水 部门id 添加3条记录
--主键表
create table mydept(
       d_id number(7) primary key,
       d_name varchar2(50) 
)

insert into mydept(d_id,d_name) values(7001,'jery');
insert into mydept(d_id,d_name) values(7002,'hong');
insert into mydept(d_id,d_name) values(7003,'fruk');

select * from mydept;
--外键表
create table myemp(
       e_id number(7),
       e_name varchar2(50),
       e_sal varchar2(50),
       d_id number(7),
       constraint myemp_d_id_fk foreign key (d_id) references mydept(d_id)
)
insert into myemp(e_id,e_name,e_sal,d_id) values(1,'yiyi',3000,7001);
insert into myemp(e_id,e_name,e_sal,d_id) values(2,'jooo',10000,7001);
insert into myemp(e_id,e_name,e_sal,d_id) values(3,'pyte',8600,7002);

select * from myemp;

--删除7001号部门
delete from mydept where d_id = 7001;--不可以直接删除已经被引用的主键

delete from mydept where d_id = 7003;

--级联置空  删除主键列，外键所在列同样设置为空

--删除原有的myemp
drop table myemp;

create table myemp(
       e_id number(7),
       e_name varchar2(50),
       e_sal varchar2(50),
       d_id number(7),
       constraint myemp_d_id_fk foreign key (d_id) references mydept(d_id)on delete cascade
)

insert into myemp(e_id,e_name,e_sal,d_id) values(1,'yiyi',3000,7001);
insert into myemp(e_id,e_name,e_sal,d_id) values(2,'jooo',10000,7001);
insert into myemp(e_id,e_name,e_sal,d_id) values(3,'pyte',8600,7002);


delete from mydept where d_id = 7001;

--删除7002部门
delete from mydept where d_id = 7002;

--级联删除：删除主键列，外键所在列同样被删除
drop table myemp;


create table myemp(
       e_id number(7),
       e_name varchar2(50),
       e_sal varchar2(50),
       d_id number(7),
       constraint myemp_d_id_fk foreign key (d_id) references mydept(d_id)on delete set null
)
select * from mydept;
select * from myemp;

insert into myemp(e_id,e_name,e_sal,d_id) values(2,'jooo',10000,7001);
insert into myemp(e_id,e_name,e_sal,d_id) values(3,'pyte',8600,7002);


delete from mydept where d_id = 7002;

5.检查约束

语法

1)在列级添加约束

create table 表名(

列名 类型(m) check(条件)--系统命名

)

--5、检查约束
--在列级添加约束
create TABLE e(
       e_id NUMBER(7) PRIMARY KEY,
       e_name VARCHAR2(50) NOT NULL,
       e_age NUMBER(3) CHECK(e_age BETWEEN 0 AND 120)
       --e_age NUMBER(3) CHECK(e_age >=0 AND e_age <= 120)
);

INSERT INTO e VALUES(1,'tom',200);--不行
INSERT INTO e VALUES(1,'tom',20);

2)追加检查约束

alter table 表名

add constraint 约束名 check(列名 条件)；

--追加检查约束
CREATE TABLE eee(
       e_id NUMBER(7) PRIMARY KEY,
       e_name VARCHAR2(50) NOT NULL,
       e_age NUMBER(3)
)


ALTER TABLE eee
ADD CONSTRAINT eee_e_age_ch CHECK(e_age BETWEEN 0 AND 120);


INSERT INTO eee VALUES(1,'tom',200);--不行
INSERT INTO eee VALUES(1,'tom',20);

3)删除检查约束

alter table 表名

drop constraint 约束名;

--删除
ALTER TABLE eee
DROP CONSTRAINT eee_e_age_ch;

INSERT INTO eee VALUES(2,'tom',200);

4)联合主键 比较少见 两个或两个以上的字段同时做主键

插入数据要求：不可为空，多个字段只要有一个字段不重复即可

--联合主键 比较少见 两个或两个以上的字段同时做主键
CREATE TABLE f(
       id1 NUMBER(7),
       id2 NUMBER(7),
       f_name VARCHAR2(50),
       CONSTRAINT f_id1_id2_pk PRIMARY KEY(id1,id2)   
)

INSERT INTO f VALUES(NULL,1,'tom');--一个空值不可行
INSERT INTO f VALUES(1,NULL,'tom');--一个空值不可行
INSERT INTO f VALUES(NULL,NULL,'tom');--两个空值不可行
INSERT INTO f VALUES(1,1,'tom');--可加入
INSERT INTO f VALUES(1,1,'tom');--重复的值不可行
INSERT INTO f VALUES(1,2,'tom');--有一个值不同可以加入

5)物理删除和逻辑删除

逻辑删除：就是通过改变某个字段，从而不显示一些数据

物理删除：就是直接将数据从数据库中删除

--逻辑删除和物理删除
create table h(
       h_id NUMBER(7) PRIMARY KEY,
       h_name VARCHAR2(8) NOT NULL,
       h_age number(3) CHECK(h_age BETWEEN 0 AND 120),
       h_status NUMBER(3) DEFAULT 0 --表示数据的状态：0正常状态  1：禁用状态
)

INSERT INTO h VALUES(1,'tom',18,0);
INSERT INTO h VALUES(2,'jack',18,0);
INSERT INTO h VALUES(3,'rose',18,0);
INSERT INTO h VALUES(4,'xiaoming',18,0);


--查询正常状态的数据
SELECT * FROM h WHERE h_status = 0;


--查询禁用状态的数据
SELECT * FROM h WHERE h_status = 1;

--数据tom非法，需要禁用
UPDATE h SET h_status = 1 WHERE h_name = 'tom';--逻辑删除：通过字段将某些数据不可见，就是一种修改

delete FROM h WHERE h_name = 'tom';--物理删除：从物理地址上将其移除

SELECT  * FROM h;