oracle添加约束[亲测有效]

小小码 • 2023-03-27 18:30 • 代码基础 • 阅读 163

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说oracle添加约束[亲测有效],希望您对编程的造诣更进一步.

oracle添加约束[数据库教程]

//建测试表
create table dept( —–部门表
deptno number(3) primary key,
dname varchar2(10),
loc varchar2(13)
);
create table employee_info( —-emplpoyee表
empno number(3),
deptno number(3),
ename varchar2(10),
sex char(1),
phone number(11),
address varchar2(50),
introduce varchar2(100)
);
—
//0.重命名
//0.1 表:rename dept to dt; ––将dept重命名为dt；
rename dt to dept; —将dt改回dept表
//0.2 列:alter table dept rename column loc to location;
alter table dept rename column location to loc;
//1.添加约束
//1.1 primary key
alter table employee_info add constraint pk_emp_info primary key(empno);
//1.2 foreign key
alter table employee_info add constraint fk_emp_info foreign key(deptno)
references dept(deptno);
//1.3 check
alter table employee_info add constraint ck_emp_info check
(sex in (‘F‘,‘M‘));
//1.4 not null
alter table employee_info modify phone constraint not_null_emp_info not null;
//1.5 unique
alter table employee_info add constraint uq_emp_info unique(phone);
//1.6 default
alter table employee_info modify sex char(2) default ‘M‘;
//2.添加列
alter table employee_info add id varchar2(18);
alter table employee_info add hiredate date default sysdate not null;
//3.删除列
alter table employee_info drop column introduce;
//3.修改列
//3.1 修改列的长度
alter table dept modify loc varchar2(50);
//3.2 修改列的精度
alter table employee_info modify empno number(2);
//3.3 修改列的数据类型
alter table employee_info modify sex char(2);
//3.4 修改默认值
alter table employee_info modify hiredate default sysdate+1;
//4.禁用约束
alter table employee_info disable constraint uq_emp_info;
//5.启用约束
alter table employee_info enable constraint uq_emp_info;
//6.延迟约束
alter table employee_info drop constraint fk_emp_info;
alter table employee_info add constraint fk_emp_info foreign key(deptno)
references dept(deptno)
deferrable initially deferred;
//7.向表中添加注释
comment on table employee_info is ‘information of employees‘;
//8.向列添加注释
comment on column employee_info.ename is ‘the name of employees‘;
comment on column dept.dname is ‘the name of department‘;
//9.清除表中所有数据
truncate table employee_info;
//10.删除表
drop table employee_info;

oracle添加约束

原文地址：https://www.cnblogs.com/linyu51/p/13379640.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7076.html

oracle添加约束[亲测有效]

相关推荐

发表回复