数据库中创建表（包括创建主键,外键,⾮空列,唯⼀）创建主键(三种⽅法)****************创建学⽣表:第⼀种:create table student(sno char(5) primary key,/*学号*/ /*可以直接指定主键*/sname char(20) not null,/*姓名*/ssex char(3) not null,/*性别*/sage integer not null,/*年龄*/sdept char(15);/*系别*/第⼆种:create table student(sno char(5) not null,constraint pk_student /*可以指定主键名称*/primary key (sno),sname char(20) not null ,/*⾮空,不可重复*/ssex char(3) not null,sage integer ,sdept char(15));第三种:创建课程表:create table course(cno char(5),/*课程号*/cname char(20) not null unique,/*课程名,⾮空,不可重复*/cpno char(5),/*前置课程号(学此课之前必修课)*/ccredit number);/*学分*/通过修改表,设置主键.alter table courseadd constraint pk_courseprimary key (cno);*************创建外键(3种⽅法)第⼀种*************创建"学⽣-课程-成绩"表create table sc(sno char(5) constraint fk_student /*第⼀种,写在属性定义⾥⾯的*/ /*可以指定外键名称,*/references student(sno),cno char(5),foreign key(cno) /*可省略外键名称*/references course(cno),grade number);**********第⼆种**********create table sc(sno char(5),constraint fk_student /*第⼆种,写在属性定义外⾯的*/foreign key (sno)references student(sno),cno char(5),foreign key(cno)references course(cno),grade number);***********第三种***********alter table scadd constraint fk_studentforeign key(sno)references student(sno);****************************建好表之后,新增或修改,删除约束****************************先⽤此语句查看某个表的所有约束:select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints WHERE TABLE_name=upper('&TABLE_Name');0.增加not null约束alter table student modify sname not null;1.修改null约束alter table student modify sname null;/*姓名可以为空*/2.增加主键约束alter table studentadd constraint pk_studentprimary key(id);3.删除主键约束先⽤此语句查看某个表的所有约束:select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints WHERE TABLE_name=upper('&TABLE_Name');这⾥要删除的是约束"fk_student"alter table drop constraint fk_student;4.增加外键约束alter table scadd constraint fk_studentforeign key(sno)references student(sno);5.删除外键约束需要知道要删除的外键名称,先⽤此语句查看某个表的所有约束:select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints WHERE TABLE_name=upper('&TABLE_Name');alter table drop constraint fk_student;6.添加unique约束alter table course modify cname unique;添加unique的同时,会⾃动创建⼀个相同名称的索引.7.取消uniquealter table coursedrop constraint sys_c005472;8.删除索引查找要删除的索引名称运⾏此语句:select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;然后运⾏drop index sys_coo5472;今天在对⼀个表,对其中⼀个属性,新建unique约束,但是提⽰:ora-02261:such unique or primary key already exists in the table原因是该属性已经是primary key了。
