Oracle（九）五种约束

Oracle（九）五种约束

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

create table myemp( empno number(4),
ename varchar(10) not null,
hiredate date constraint emp_hiredate_nn not null
);

insert into myemp values(7075, 'zk', sysdate);
insert into myemp values(7075, null, sysdate);

【这里constraint是定义列级的约束】

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

create table myemp(
empno number(4),
ename varchar(10) not null,
email varchar(20) ,
constraint emp_email_uk unique(email)
);
insert into myemp values(7777, 'z', 'lxr@163.com');
insert into myemp values(7777, 'zz', 'lxr@163.com');

【这里constraint是定义表级的约束】

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

create table myemp(
  empno number(4),
  ename varchar(10) not null,
  constraint emp_empno_pk primary key(empno)
);
insert into myemp values(7075, 'zk');

注：也可以这样定义--empno number(4) primary key,

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

create table myemp(
  empno number(4) primary key,
  ename varchar(10) not null,
  deptno number(2),
  constraint emp_deptno_fk foreign key (deptno)
  references dept(deptno)
);

添加级联删除：

1）on delete cascade

create table myemp(
  empno number(4) primary key,
  ename varchar(10) not null,
  deptno number(2),
  constraint emp_deptno_fk foreign key (deptno)
  references dept(deptno) on delete cascade
);

insert into myemp values(7075, 'zk',80);
insert into myemp values(7077, 'zk',80);
select * from myemp;
部门号为80的删除了之后，myemp中部门号为80的员工也均被删除。
delete from dept where deptno = 80;

2）on delete set null

create table myemp(
  empno number(4) primary key,
  ename varchar(10) not null,
  deptno number(2),
  constraint emp_deptno_fk foreign key (deptno)
  references dept(deptno) on delete set null
);

insert into myemp values(7075, 'zk',70);
insert into myemp values(7077, 'zk',70);
select * from myemp;
部门号为70的删除了之后，myemp中部门号为70的员工的部门号会变成null。
delete from dept where deptno = 70;

5、check约束---------------------------------

create table myemp(
  empno number(4) primary key,
  ename varchar(10) not null,
  sex varchar(5) constraint emp_sex check(sex in ('man','women'))
);