oracle中的字段约束和表字段表名的修改等（四）

1. oracle中的约束，表级约束和列级约束

--oracle中的约束，表级约束和列级约束
/*
       主键约束 primary key constraint
       唯一性约束 unique constraint
       默认约束 default constraint
       非空约束 not null constraint
       检查约束 check constraint
       外部键约束 foreign key constraint

*/
drop table student;
create table student(
       sid number(8,0) primary key,
       name varchar2(20),
       sex char(2),
       birthday date,
       address varchar2(50)
);
commit;
drop table student;
create table student(
       sid number(8,0) ,
       name varchar2(20) not null,
       sex char(2) not null,
       birthday date constraint birthday_c not null,
       address varchar2(50) not null,
       constraint sid_pk primary key(sid)
);
--如果在创建表时为创建主键约束
--添加主键约束
alter table student add constraint sid_pk primary key(sid);
commit;

--添加非空约束
alter table student modify birthday not null;
--删除非空约束
alter table student modify birthday null;
--删除约束disable\enable\drop(彻底删除) constraint 约束名称；drop primary key;删除主键约束
alter table student disable constraint sid_pk;
alter table student enable constraint sid_pk;
alter table student drop primary key;
alter table student drop constraint sid_pk;
--唯一性约束，允许为空,创建表时添加唯一性约束
drop table student;
commit;
create table student(
       sid number(8,0) unique,
       name varchar2(20) constraint name_n not null,
       constraint name_c unique(name),
       constraint sid_k primary key(sid)
);

--清除pl/sql缓存
alter system flush buffer_cache;

--修改数据表时添加唯一性约束
alter table student add constraint sid_u unique(sid);

--检查约束（例如性别列）
--创建表时如何设置检查约束
drop table student;
create table student(
       sid number(8,0) ,
       name varchar2(20) not null,
       sex char(2) check(sex='男' or sex='女'),--列级约束
       birthday date constraint birthday_c not null,
       address varchar2(50) not null,
       constraint sid_pk primary key(sid)
);

drop table student;
create table student(
       sid number(8,0) ,
       name varchar2(20) not null,
       sex char(2) not null,
       birthday date constraint birthday_c not null,
       address varchar2(50) not null,
       constraint sid_pk primary key(sid)，
       constraint ck_sex check(sex='男' or sex='女')--表级约束
);

---修改表时添加检查约束
alter table student add constraint ck_sex check(sex='男' or sex='女');
--外键约束，外键主要是连接表之间的关系，看那个表先创建，先创建的表叫主表，后创建的表为从表,外键中对应的主表字段绝对是主键
--主外键字段值对应相同
drop table classinformation;
create table classinformation(
       sid number(10,0) primary key,
       classname varchar2(20)
);
--列级设置外键
drop table studentinformation;
create table studentinformation(
       id number(10,0),
       sid number(10,0) references classinformation(sid) on delete cascade,
       name varchar2(10)
);
--表级设置外键
create table studentinformation(
       id number(10,0),
       sid number(10,0),
       name varchar2(10),
       constraint fk_sid foreign key(sid) references classinformation(sid) on delete cascade
);
--修改增加外键
alter table studentinformation add constraint fk_sid foreign key(sid) references classinformation(sid) on delete cascade;
alter table studentinformation drop constraint fk_sid;

--表操作--
alter table student add tel varchar2(11);--添加列
alter table student modify tel number(11,0);--修改列类型
alter table student drop column tel; --删除列
alter table student rename column tel to phone; --修改列名

rename student to students;--修改表名
select * from students;
--删除表
truncate table students;--主要是清空表数据，不删除表结构（被称为截断删除）
drop table students;--连同表结构一同删除