Oracle数据库的基本操作添加/修改约束以及表结构的修改序列

发表于 | 分类于 | 热度:
字数统计: 2.9k 字 | 阅读时长 ≈ 12 分钟

1.约束

表的约束

往表中插入数据的时候，可能出现一些问题，比如：重复插入数据，内容不对（性别）
——-如何保证数据库表中数据的完整性和一致性呢？约束
常见的约束：
主键（primary key），外键（foreign key）,唯一（unique）,非空（not null）,默认（default）,用户自定义（check）

2. 约束分类

—–常见的约束有：

  • 主键约束（primary key），
  • 外键约束（foreign），
  • 唯一（unique），非空（not null）
  • 默认（default）
  • 用户自定义约束（check）

    2.1 primary key 主键约束

什么是主键约束？ 主键约束就是给表定义一个主键，什么是主键？？
主键只要是用来保证表记录的唯一非空的。

主键（PRIMARY KEY ）约束：唯一的标识表中的每一行，不能重复，不能为空。 创建主键或唯一约束后，ORACLE 会自动创建一个与约束同名的索引（UNIQUENES 为UNIQUE 唯一索引）。需要注意的是：每个表只能有且有一个主键约束。

什么是主键约束？主键约束就是给表定义一个主键，什么是主键？
--主键主要是用来保证表记录的唯一非空的。

建表的时候添加主键

1
2
create table student(stuno number(4) primary key,stuname varchar2(10),age number(2),addr varchar(50));
insert into student values(1001,'zhang',18,'luoyang');

再次插入相同记录，违法主键约束，不允许插入

1
2
3
SQL> insert into student values(1001,'zhang',18,'luoyang');
insert into student values(1001,'zhang',18,'luoyang')
ORA-00001: 违反唯一约束条件 (SYS.SYS_C0010797)

如果主键为null也不允许插入，同样是违反了主键约束

1
2
SQL> insert into student values(null,'zhang',18,'luoyang');
insert into student values(null,'zhang',18,'luoyang')

创建一张课程表：

1
create table course(cno number(4) primary key,cname varchar2(20),cscore number(2));

–创建一张成绩表：学号和课程号共同作为一个主键，称为联合主键
一张表只能有一个主键

1
2
3
create table score(sno number(4),cno number(4),score number(5,2)，constraint pk_score primary key (sno,cno));
drop table course;
create table course(cno number(4) constraint pk_course primary key ,cname varchar2(20),cscore number(2));
特别说明：不推荐大家使用复合主键。

2.2 unique 唯一

唯一（UNIQUE ）约束：在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。否则就会违法约束条件。用于指定列的值不能重复，可以为 null

1
2
3
4
举例：
create table user2(id number unique,name varchar2(30));
insert into user2 values(1,111);//id 输入重复的值是会报错
注意：oracle 中 中 unique  可以为 null ，而且允许多行为 nul

唯一约束：

–问题：student(sno,sname,age,addr,idcard)主键：sno，idcard(身份证号)：可以为空，但是如果写的话要求唯一
–创建唯一约束：

  1. (1)创建唯一约束方法一
1
2
3
4
5
6
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18) unique);
  1. 2)创建唯一约束方法二
1
2
3
4
5
6
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18) constraint uk_idcard unique);
  1. （3）创建唯一约束方法三
1
2
3
4
5
6
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18),constraint uk_idcard unique(idcard));

2.3 not null 非空

非空（NOT NULL ）约束：顾名思义，所约束的列不能为 NULL 值。否则就会报错

1
2
3
举例：
create table user1(id number,name varchar2(30) not null);
insert into user1 values(001,'');//会报错

–非空约束和默认约束

1
2
3
4
5
6
create table student(
sno number(4) primary key,   --主键约束
sname varchar2(20) not null, --非空约束
age number(3) default 18,    --默认约束
addr varchar2(50),
idcard number(18) unique);   --唯一约束

2.4 foreign key 外键约束 references

外键（FOREIGN KEY ）约束：用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性.能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库； 用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必需具有主键约束或是 e unique 约束，当定义外键约束后，要求外键列数据必需在
主表的主键列存在或是为 null

外键约束 外键 在另外一张表中是主键
student(sno sname,age,addr.udcard) 主键sno
course （cno，cname ccouse）主键sno
score （sno cno score）主键（sno ，cno） 外键：一个是sno 一个是cno
——–外键是做什么用的？如果student中没有学号1001学生信息，那么score中也不应该有该学生成绩
在score插入数据的时候，如果学生student中不存在对应学号或者课程表中不存在对应课程 则不允许插入

1
2
3
4
5
6
7
8
9
10
11
create table class(id number primary key,name varchar2(32));
create table stus(id number primary key,
name varchar2(36) not null,
classid number references class(id)
);
特别说明：froeign key  外键的细节
1 、外键指向主键列；
2 、外键可以指向 unique  列；
3 、建表时先建主表，再建从表；删除表先删从表，再删主表。
4 、外键列属性值要与主键或 unique  列属性值的类型保持一致
5 、外键列的值，必需在主键列中存在。但外键列的值允许为 null

**外键约束：

外键：在另外一张表中是主键**

–student(sno,sname,age,addr,idcard) 主键：sno
–course(cno,cname,cscore) 主键：cno
–score(sno,cno,score)主键： (sno,cno) 外键：sno 外键: cno

外键做什么用？如果student表中没有1001学生信息，那么score表中也不应该有该学生的成绩。

往成绩表插入数据的时候，如果学生表中不存在对应的学号，或者课程表中不存在对应的课程，则不允许插入

1
2
3
4
5
6
7
8
9
10
11
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18));
 
create table course(
cno number(4)primary key,
cname varchar2(20),
cscore number(2));

1.创建外键约束的第一种方式

1
2
3
4
5
create table score(
stuno number(4) references student(sno),   --创建第一个外键约束
couno number(4) references course(cno),    --创建第二个外键约束
score number(5,2),
constraint pk_score primary key(stuno,couno));  --创建一个主键约束

2.创建外键约束的第二种方式

1
2
3
4
5
create table score(
stuno number(4) constraint fk_stuno references student(sno),  --创建第一个外键约束
couno number(4) constraint fk_couno references course(cno),   --创建第二个外键约束
score number(5,2),
constraint pk_score primary key(stuno,couno));

3.创建外键约束的第三种方式

1
2
3
4
5
6
create table score(stuno number(4),
couno number(4),
score number(5,2),
constraint fk_stuno foreign key(stuno) references student(sno),
constraint fk_couno foreign key(couno) references course(cno),
constraint pk_score primary key(stuno,couno));

外键约束总结
–总结1：在一张表中只能有一个主键，但是可以有多个外键。
–总结2：作为外键的字段名，不一定必须和被参考表一致。
–总结3：定义了外键约束之后，往表中插入一条记录，外键值会参考其父表中主键的值，只有父表中存在该值才能插入成功

–往student表中插入3条记录

1
2
3
insert into student values(1001,'zhangsan',19,'henanluoyang',98765432123456789);
insert into student values(1002,'lisi',19,'henanluoyang',98765432123456789);
insert into student values(1003,'wangwu',19,'henanluoyang',98765432123456789);

查看student表中所有内容

1
select * from student;

查看course表中所有记录

1
select * from course;

–思考：insert into score values(1003,9997,89);能成功吗？不能，因为course表中不存在课程号为9997的课程

–思考：insert into score values(1003,9998,89);能成功吗？可以，因为1003在student表中存在，9998在course表中存在

–总结4：定义外键约束之后，删除父表中的记录，需要先将相关子表的记录删除

1
2
3
4
delete from student where sno=1003; --删除不掉，提示： 违反完整约束条件 (SYS.SYS_C0010808) - 已找到子记录
--正确的做法：
delete from score where stuno=1003;  --先删子记录
delete from student where sno=1003;  --后删父记录

2.5 check 检查，用户自定义约束

条件（CHECK ）约束：表中每行都要满足该约束条件。条件约束既可以在表一级定义
也可以在列一级定义。在一列上可以定义任意多个条件约束

1
2
3
4
5
举例：
create table user4(id number primary key,
sal number check(sal>=1000 and sal<=2000),
sex char(2) check(sex in('男','女')));
insert into user4 values(1,1000,' 男');//sal  列的值不满足 1000 至 至 2000

用户自定义约束(检查约束)
check(约束条件)

1
2
3
4
5
6
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3) default 18 check(age between 10 and 45),   ---定义学生年龄默认为18，自定义约束条件为在10到45岁之间
addr varchar2(50),
idcard number(18) unique);                               ------身份证号唯一性约束条件

定义学生性别必须为男或者女

1
2
3
4
5
6
7
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
gender char(2) check(gender in('男','女'))，                   -------定义学生性别必须为男或者女
age number(3) default 18 check(age between 10 and 45),
addr varchar2(50),
idcard number(18) unique);

总结：定义了完整性约束之后，表中记录的插入，删除，修改必须符合表的完整性约束条件，
如果违反了完整性约束条件，则不允许操作

二、Oracle修改表结构

修改表结构

–问题：给student表添加字段regdate类型是date;

1
2
alter table student add regdate date;
alter table student add province varchar2(350);

删除刚才添加的字段

1
2
alter table student drop column regdate;
alter table student drop(province,regdate);

–修改字段类型和长度

1
2
alter table student modify idcard char(18);
alter table student modify idcard char(20);

在这里插入图片描述
修改表名

1
alter table student rename to stu;

修改字段名

1
alter table stu rename column gender to sex;

4.给表添加约束

知识点4：如果建表的时候，没有添加约束，那么建好表之后，如何给表添加约束？
drop table stu cascade constraints;

1
2
3
4
5
6
7
create table student(
sno number(4),
sname varchar2(20),
gender char(2)，
age number(3),
addr varchar2(50),
idcard number(18));

–添加主键约束

alter table student add constraint pk_sno primary key(sno);

–添加唯一约束

alter table student add constraint uk_idcard unique(idcard);

–添加检查约束

alter table student add constraint ck_age check(age between 18 and 50);

–添加非空约束

alter table student modify sname not null;

–添加默认约束

alter table student modify age default 18;
create table score(sno number(4),score number(5,2));

–添加外键约束
alter table score add constraint fk_sno foreign key(sno) references student(sno);

–禁止某个约束

alter table score disable constraint fk_sno;

–启用某个约束

alter table score enable constraint fk_sno;

–删除约束

alter table score drop constraint fk_sno;

–删除student表的主键约束：

alter table student drop primary key;

5.序列

–序列是什么？有序的数字组成的一个排列 1 2 3 4 5 6 7
–做什么用？序列是一个独立的数据库对象，主要用来生成主键
–怎么用?

–创建序列：

create sequence seq01 --seq01序列名
start with 3          --序列的起始值
increment by 1        --步长
maxvalue 9999         --序列的最大值
minvalue 0            --序列的最小值
nocycle               --nocycle(表示序列不循环)|cycle(表示序列循环使用)
cache 20;             --cache 20(为了加快序列的生成速度，每次生成20个值，放到缓存中)|nocache（不往缓存中存放序列值，使用一次，生成一次）

–currval:返回序列的当前值，不会引起序列自增
–nextval：返回序列的下一个值，会引起序列自增
–查看序列的下一个值，会引起序列自增
select seq01.nextval from dual;
–第一次使用序列的时候，必须使用 序列名.nextval

–查看序列的当前值
select seq01.currval from dual;

–序列的使用：

1
insert into student values(seq01.nextval,'zhang','男',18,'河南信阳','123456789987654321');

如果下一个表还要引用这个序列则接着这个序列自动增加。

–注意事项：一个sequence可以供多张表使用的。可能造成主键值的不连续。

–删除序列：
drop sequence seq01;

整理不易，转载请注明出处。

本文标题:Oracle数据库的基本操作添加/修改约束以及表结构的修改序列

文章作者:雷凯博

发布时间:2018年02月19日 - 16:02

最后更新:2021年05月16日 - 19:05

原始链接:http://yoursite.com/2018/02/19/2018-02-10-Oracle%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C--%E6%B7%BB%E5%8A%A0%E4%BF%AE%E6%94%B9%E7%BA%A6%E6%9D%9F%E4%BB%A5%E5%8F%8A%E8%A1%A8%E7%BB%93%E6%9E%84%E7%9A%84%E4%BF%AE%E6%94%B9&%E5%BA%8F%E5%88%97/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

坚持原创技术分享，您的支持将鼓励我继续创作！
-------------本文结束感谢您的阅读-------------
0%