约束
(1)主键约束
--主键用来保证字段的唯一非空
--一张表中只能有一个主键
--由多个字段组成的主键被称为联合主键
创建主键:
create table test1(
id number(4) primary key,
name varchar2(20)
);
create table test2(
id number(4) constraint pk_test2 primary key,
name varchar2(20)
);
create table test3(
id number(4),
name varchar2(20),
constraint pk_test3 primary key(id)
);
创建联合主键:
create table students(
stuno number(4) constraint pk_students primary key,
stuname varchar2(20),
age number(2),
addr varchar2(50)
);
create table courses(
couno number(4) primary key,
cname varchar2(20),
cscore number(5,2)
);
create table scores(
sno number(4),
cno number(4),
score number(5,2),
constraint pk_scores primary key(sno,cno)
);
(2)外键约束
--往成绩表插入数据的时候,如果学生表中不存在对应的学号,或者课程表中不存在对应的课程号,则不允许插入
创建外键:
create table scores(
sno number(4) references students(stuno),
cno number(4) references courses(couno),
score number(5,2),
constraint pk_scores primary key(sno,cno)
);
create table scores(
sno number(4) constraint fk_students references students(stuno),
cno number(4) constraint fk_courses references courses(couno),
score number(5,2),
constraint pk_scores primary key(sno,cno)
);
create table scores(
sno number(4),
cno number(4),
score number(5,2),
constraint fk_students foreign key(sno) references students(stuno),
constraint fk_scourses foreign key(cno) references courses(couno),
constraint pk_scores primary key(sno,cno)
);
--定义外键约束后,要想删除父表(students,courses)中的记录,需要先将相关子表(scores)的记录删除
(3)唯一约束
create table students(
stuno number(4) primary key,
stuname varchar2(20),
age number(2),
addr varchar2(50),
idCard varchar2(20) unique
);
(4)非空约束
create table students(
stuno number(4) primary key,
stuname varchar2(20) not null,
age number(2),
addr varchar2(50),
idCard varchar2(20) unique
);
(5)默认约束
create table students(
stuno number(4) primary key,
stuname varchar2(20) not null,
age number(2) default 18,
addr varchar2(50),
idCard varchar2(20) unique
);
(6)自定义约束(检查约束)
create table students(
stuno number(4),
stuname varchar2(20),
gender varchar2(2) check(gender in('男','女')),
age number(2) default 18 check(age between 18 and 45),
--age number(2) default 18 check(age>=18 and age<=45),
addr varchar2(50),
idCard varchar2(20) unique
);
添加约束
--添加主键约束
alter table students add constraint pk_students primary key(stuno);
alter table scores add constraint pk_score primary key(sno,cno);--添加联合主键
--添加外键约束
alter table scores add constraint fk_sno foreign key(sno) references students(stuno);
--唯一约束
alter table students add constraint un_idCard unique(idCard);
--非空约束
alter table students modify stuname not null;
--默认约束
alter table students modify age default 18;
--禁用约束
alter table students disable constraints pk_stuno;
--启动约束
alter table students enable constraints pk_stuno;
--删除约束
alter table students drop constraints pk_stuno;
序列
--创建序列
create sequence seq01
start with 1 --序列起始值为1
increment by 1 --步长为1
minvalue 0
maxvalue 9999
nocycle --cycle/nocycle
cache 20; --cache/nocache
cache是为了加快序列生成速度,每次生成20个值放到缓存中
nocache不往缓存中存放序列值,使用一次生成一次
currval:返回序列的当前值,不会引起序列自增
nextval:返回序列的下一个值,序列自增
select seq01.nextval from dual;
select seq01.currval from dual;
--序列的使用
insert into students values(seq01.nextval,'zhang','男',20,'luoyang','123456');
--删除序列
drop sequence seq01;
--序列是独立的,不受表的影响