Oracle四大约束

drop table person;


--创建表时使用unique与check约束。
create table person(
id varchar2(8) primary key,
name varchar2(10) not null,
sex varchar(6),
age number,
constraint sex_check check (sex in ('male','female')),
constraint name_unique unique(name) 
);
--使用alter语句更改属性的check与unique与主键约束。
alter table person add check(sex in ('male','female'));
alter table person add primary key(id);
alter table person add unique(name);
.
desc person;


insert into person(id,name,sex,age) values('0844131','zxk','male',18);
insert into person(id,name,sex,age) values('0844141','null','male',18);
insert into person(id,name,sex,age) values('0844151','lyk','female',18);


update person set name = 'xyh' where id='0844141' ;
update person set age = 20 where id = '0844151' ;
update person set age = 15 where id = '0844141' ;


select * from person; 


create table type(
tid number(4) primary key,
tname varchar2(10) not null
);


create table book(
bid number(4) primary key,
bname varchar2(20) not null,
tid number(4),
constraint book_type foreign key(tid) references type (tid),
constraint bname_unique unique(bname)
);


insert into type(tid,tname)values(1,'历史类');
insert into type(tid,tname)values(2,'数学类');
insert into type(tid,tname)values(3,'计算机类');
insert into book(bid,bname,tid) values(1,'史记',1);
insert into book(bid,bname,tid) values(4,'24史',1);
insert into book(bid,bname,tid) values(2,'高数',2);
insert into book(bid,bname,tid) values(3,'java',3);


select * from type;
select * from book;


--create or replace  index name_index on person (name) tablespace system;




create view type_book_view as select bid,bname,type.tid,tname from book,type where type.tid=book.tid;


select * from type_book_view;


create view type_book as select bid,bname from book with check option;


select * from type_book;


select column_name,insertable,updatable,deletable from user_updatable_columns where table_name='TYPE_BOOK';


insert into type_book(bid,bname) values(5,'web');


create table student (
sid number(4) primary key,
sname varchar2(8) not null
);


drop table  student;
create sequence stu_seq start with 1 increment by 1 nocache nocycle order;


insert into student (sid,sname) values ( stu_seq.nextval,'zxk'); 
insert into student (sid,sname) values ( stu_seq.nextval,'lyk'); 
insert into student (sid,sname) values ( stu_seq.nextval,'xyh'); 


select * from student ;