Oracle（十） 使用Alter Table 语句添加约束

Oracle（十） 使用Alter Table 语句添加约束 

1、not null----------------------------------

create table myemp(
  empno number(4),
  ename varchar(10)
);

将ename列属性调整为不可为空，修改成not null 只针对存在的列
alter table myemp
modify ename constraint myemp_ename_nn not null

insert into myemp values(7076, 'man');
insert into myemp values(7077, null);

2、unique----------------------------------

create table myemp(
  empno number(4),
  ename varchar(10) not null,
  sex varchar(5)
);

alter table myemp
add constraint myemp_empno_uk unique(ename);

insert into myemp values(7077, 'zk', 'man');
insert into myemp values(7078, 'zk', 'man');

3、primary key----------------------------------

alter table myemp
add constraint myemp_empno_pk primary key(empno);

4、foreign key----------------------------------

create table myemp(
  empno number(4),
  ename varchar(10),
  sex varchar(5),
  deptno number(2)
);

alter table myemp
add constraint myemp_empno_fk foreign key(deptno) references dept(deptno);

5、删除约束----------------------------------

alter table myemp
drop constraint myemp_ename_nn;

优先级：Where > Group BY > Having > Order by

select e.empno, e.ename, (e.sal+nvl(e.comm, 0))"总收入" from emp e where to_char(e.hiredate, 'yyyy') = '1981';

select e.deptno from emp e where e.hiredate > to_date('1981/01/01','yyyy/mm/dd');