values(1002);
---------------------------------------------------emial列 unique---------------------------------------------- drop table student_tian; create table student_tian(
id number(4) primary key, name char(10) not null, email char(20) unique );
insert into student_tian(id,name,email) values(1001,'tian','123@tarena.com');
----报错 ORA-00001 unique constraint violated 唯一的约束被违反
insert into student_tian(id,name,email) values(1002,'yang','123@tarena.com');
----------------------------------------gender列 check-------------------------------------------------- ---gender: 只允许 'M' 男 'F' 女 drop table student_tian; create table student_tian(
id number(4) primary key, name char(10) not null, email char(20) unique,
gender char(1) check (gender in ('M','F')) );
insert into student_tian(id,name,email,gender) values(1001,'tian','123@tarena.com','F'); insert into student_tian(id,name,email) values(1002,'yang','1234@tarena.com');
---报错：ORA-02290 check constaint violated
insert into student_tian(id,name,email,gender) values(1002,'yang','1234@tarena.com','A');
desc student_tian -----------------------查看表结构 user_tables ------------------------查看用户表 user_objects ------------------------查看用户对象 user_procedure ------------------------查看用户过程 user_constraints -------------------------查看约束条件
select constraint_name,constraint_type from user_constraints where table_name = 'STUDENT_TIAN';
--------------------约束条件constraint 表名_列名_约束类------------------------ ---- 列级约束
drop table student_tian; create table student_tian(
id number(4) constraint stu_t_id_pk primary key, name char(10) constraint stu_t_name_nu not null,
email char(20) constraint stu_t_email_uk unique,
gender char(1) constraint stu_t_gender_ck check (gender in ('M','F')) );
---- 表级约束
drop table student_tian; create table student_tian(
id number(4) ,
name char(10) constraint stu_t_name_nu not null, email char(20) , gender char(1)
constraint stu_t_id_pk primary key (id)
constraint stu_t_email_uk unique (email)
constraint stu_t_gender_ck check (gender in ('M','F')) );
---- 建表，除了非空以外的约束条件，全部放在建表以后在建 drop table student_tian; create table student_tian(
id number(4) ,
name char(10) not null, email char(20) , gender char(1) );
--------------------------------------- 建完表以后添加约束条件-------------------------------------- alter table student_tian add
constraint stu_t_id_pk primary key (id);
alter table student_tian add
constraint stu_t_email_uk unique (email);
alter table student_tian add
constraint stu_t_gender_ck check (gender in ('M','F'));
----专业表
create table major_tian(
id number primary key, name char(20) not null );
insert into major_tian values(1,'Java'); insert into major_tian values(2,'Oracle'); insert into major_tian values(3,'C++'); insert into major_tian values(4,'android'); commit;
select * from major_tian;
create table stu_tian( id number(4),
name char(10) not null, mid number(2) );
alter table stu_tian add constraint stu_tian_id_pk primary key (id);
alter table stu_tian add constraint stu_tian_mid_fk foreign key (mid) references major_tian;
select constraint_name,constraint_type from user_constraints where table_name = 'STU_TIAN';
insert into stu_tian values(1001,'甲亢',1); insert into stu_tian values(1002,'乙亢',2);
----报错 ORA-02291 integrity constraint (OPENLAB.STU_TIAN_MID_FK) violated - parent key not found 父键找不到 insert into stu_tian values(1003,'抗体',10);
update stu_tian
set mid = 8 where mid = 1;
----报错 ORA-02291 integrity constraint (OPENLAB.STU_TIAN_MID_FK) violated - child key record found 子表中已有引用 delete major_tian where id=1;
------------------???????????????? 约束条件 ?????????????????---------------------- 主键 pk = not + null + unique 外键 fk :表间的一对多关系 非空 not null 唯一 unique 检查 check
-------------------- 联合主键: ???两者联合起来没有重复??----------------------------------?
? last_name first_name
张 三 张 三丰 Smith john
smith tom
create table student_tian( first_name char(10), last_name char(10), score number );
alter table stu_tian add
constraint stu_ln_fn_pk primary key (last_name,first_name); alter table stu_tian add
constraint stu_age_ck check ( age > 17 );
????------????? check 实际用的比较少，因为正常情况sql语句在程序中运行，check功能可以通过程序实现
insert into stu_tian values(1,'a',2); insert into stu_tian values(2,'b',2); insert into stu_tian values(3,'c',1);
alter table stu_tian drop
constraint stu_tian_mid_fk; alter table stu_tian add
constraint stu_tian_mid_fk foreign key (mid) references major_tian
on delete set null;----------将参照了被删除的键值的键值设置为null
---??删除主表id = 2的记录，成功，把子表中所有专业2的学生mid列设置为空
-----------??????????????????????????cascade : 级联,株连?????????????????? alter table stu_tian drop
constraint stu_tian_mid_fk; alter table stu_tian add
constraint stu_tian_mid_fk foreign key (mid) references major_tian on delete cascade;
---??删除主表id = 2的记录，成功，把子表中所有专业2的学生删除 ---???????????不复制约束条件，只复制表结构和数据
----------------------------------------???赋值结构 --------------------------------------------- create table stu_tian1 as
select * from stu_tian where 1 = 0 ;
----------------------------------?????给新表增加约束条件
----------------------------------? insert into stu_tian1 (select*from stu_tian where mid = 2);
建立约束条件的几种语法： 1】.建表时，列级 --???约束条件自定义 create table student_tian(
id number(4) primary key, name char(10) not null, email char(20) unique );
2】建表时，表级
create table student_tian( id number(4),
name char(10) not null, email char(20)
constraint student_id_pk primary key (id)
constraint student_email_nu unique (email) );
3】建表以后
create table student_tian( id number(4),
name char(10) not null, email char(20) );
alter table stu_tian add
constraint student_id_pk primary key (id); alter table stu_tian add
constraint student_email_nu not null (email);
===================????????????????????????????========================
------------------------------------------------脚本文件格式---------------------------------------------------- 1】删除外键约束 2】刪表 3】建表
4】添加约束 5】添加数据 commit;
===================????????????????????????????=============