Oracle之SQL基础--设置表约束

 

约束,可以没有名字,oracle能夠自動加上.为约束取名是为了方便删除.

 

Not null 列级约束

例如：create table test(empid number(4),name varchar2(20) not null);

 

create table fangfan_Student(

 id number (4) not null,

  name varchar2(8) not null,

  phone varchar2(20) not null

);

desc fangfan_student;

在往表当中插入数据的时候,当字段设置了非空约束时候,不能插入空数据.

 

字段＋类型＋constraint+约束名称＋约束类型

约束名称不能重复

create table fangfan_Student(

 id number (4) constraint idnotnull not null,

  name varchar2(8) not null,

  phone varchar2(20) not null

);

desc fangfan_student;

drop fangfan_student;

 

UNIQUE 唯一约束

既可以定义在表上,也可以定义在列上.

 

create table fangfan_Student(

id number(8) unique,

name varchar2(8) not null,

email varchar2(20) unique

);

desc fangfan_student;

insert into fangfan_student values (10,'liuwei','fangfan@126.com');

insert into fangfan_student values (12,'liuwei','wudong@126.com');//同下错误提示

insert into fangfan_student values (10,'xufeng','xuyi@126.com');//unique constraint (OPENLAB.SYS_C0069734) violated

drop fangfan_student;

 

设置字段唯一是可以存在空值的唯一性对空值不判断所以这两条语句都可以运行成功.

insert into fangfan_student (name) values('wufa');

insert into fangfan_student (name) values('wufa');

 

create table fangfan_student1(

id number(4),

name varchar2(8) not null,

email varchar2(20) unique,

constraint id_uq1 unique (id)

);

 

create table fangfan_student2(

id number(4),

name varchar2(8) constraint namenotnull not null,

email varchar2(20) unique,

constraint id_uq2 unique (id)

);

 

主键约束：可以设置在列上也可以设置在表上

这三种方式等价

第一种

create table fangfan_student3(

id number(4) unique not null,

name varchar2(8),

email varchar2(20)

);

drop table fangfan_student3;

第二种

create table fangfan_student3(

id number(4) primary key,

name varchar2(8),

email varchar2(20)

);

drop table fangfan_student3;

 

在这里请注意定义表级主键约束名称一定不可以少.

所以下面的的一种建表将失败

create table fangfan_student3(

id number(4),

name varchar2(8),

email varchar2(20),

constraint primary key (id)

);

drop table fangfan_student3;

 

对约束是不是一定要加上关键字呢,其实不是的

create table fangfan_student3(

id number(4),

name varchar2(8),

email varchar2(20),

constraint fs_pk3 primary key (id)

);

drop table fangfan_student3;

 

在这里和mysql是一样的设置约束的关键词可以省略,这就说明如果加上这个关键词,那么一定就不能省略名称.

create table fangfan_student3(

id number(4),

name varchar2(8),

email varchar2(20),

primary key (id)

);

drop table fangfan_student3;

 

create table fangfan_student3(

id number(4) primary key,

name varchar2(8),

email varchar2(20),

constraint unique(name)

);

drop table fangfan_student3;

create table fangfan_student3(

id number(4) primary key,

name varchar2(8),

email varchar2(20),

unique(name)

);

drop table fangfan_student3;

 

为约束取名是为了方便删除.

 

 

外键

constraint emp_dept_fk FOREIGN KEY (dept_id) REFERENCES dept(dept_id);

设置了外键之后就可以级联删除.

 

create table fangfan_classroom(classroom_id number(4) PRIMARY KEY, classroom_name varchar2(20));

create table fangfan_student(student_id number(4) primary key,class_id number(4),student_name varchar2(20),score number(4),

constraint sc_fk foreign key (class_id) references fangfan_classroom(classroom_id)

);

如果想实现级联删除on delete cascade不可少

如果想实现级联设置为空则on delete set null不可少

drop table fangfan_student;

create table fangfan_student(student_id number(4) primary key,class_id number(4),student_name varchar2(20),score number(4),

constraint sc_fk foreign key (class_id) references fangfan_classroom(classroom_id) on delete cascade

);

 

drop table fangfan_student;

create table fangfan_student(student_id number(4) primary key,class_id number(4),student_name varchar2(20),score number(4),

constraint sc_fk foreign key (class_id) references fangfan_classroom(classroom_id) on delete set null

);

 

insert into fangfan_classroom values(1,'sanban');

insert into fangfan_classroom values(2,'siban');

insert into fangfan_classroom values(3,'wuban');

insert into fangfan_student values(1,1,'fangfan',100);

insert into fangfan_student values(2,1,'wudong',100);

insert into fangfan_student values(3,2,'wumei',100);

insert into fangfan_student values(4,3,'xufeng',100);

/

 

delete from fangfan_classroom where classroom_id=2;

 

clear scr

 

check约束

 

 

create table emp_fangfan(

id number primary key,

name varchar2(20) not null,

salary number(7,2)

constraint fangfan_salary_min check (salary>5000)

);

insert into emp_fangfan values(1,'fangfan',5100);

 

添加删除约束的语法

使用alter table语句

添加或者删除约束不能修改

有效化或者无效化约束

添加not null约束只能使用modify

 

如果事先没有添加约束,那么采用以下的方式添加约束

添加外键约束

alter table fangfan_student add constraint stu_class_fk foreign key (class_id) references fangfan_classroom(classroom_id);

添加主键约束

alter table fangfan_stduent add constraint fangfan_stupk primary key (student_id);

添加唯一约束

alter table fangfan_student add constraint score_fang_un unique(score);

添加非空约束

alter table fangfan_student modify (student_name varchar2(20) not null);

删除非空约束

alter table fangfan_student modify (student_name varchar2(20) null);

删除唯一约束

alter table fangfan_student drop constraint score_fang_un;

删除主键约束

alter table fangfan_student drop constraint fangfan_stupk cascade;

 

无效化约束

使用disable

alter table fangfan_student disable  constraint stu_class_fk cascade;

在无效化约束之后如果向表当中添加了不符合外键的约束时候,重新启动约束时必须删除不合法数据,不然不能启动

激活约束enable

alter table fangfan_student enable  constraint stu_class_fk cascade;

 

查看约束

保存使用约束的数据字典user_constraints

select constraint_name,constraint_type,search_condition from user_constraints where table_name='FANGFAN_STUDENT';

 

select constraint_name,column_name from user_cons_columns where table_name='FANGFAN_STUDENT';

 

修改表中字段名称

create table test(

name varchar2(20);

);

alter table test rename column name to t_name;

 

修改表名

rename test to t_test;