SQL基础学习笔记（三）—约束

create table emp2(
                  id number(10) constraint emp2_id_nn not null,
                  name varchar2(20) not null,
                  salary number(10,2)
                  )
constraint emp2_id_nn  给约束起个名增加阅读性，不写也行，会自动加上sys_XXXX

②表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）

insert into emp2
values(1001,null,2000)

insert into emp2
values(1001,null,2000)

create table emp3(
                  id number(10) constraint emp3_id_uk unique,
                  name varchar2(20) constraint emp3_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                  --表级约束
                   constraint emp3_email_uk unique(email)
                  )

create table emp4(
                  id number(10) constraint emp4_id_pk primary key,
                  name varchar2(20) constraint emp4_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                   constraint emp4_email_uk unique(email)
                  )

主键要求 非空，唯一，相当于自动加上了not null，unique的限制

create table emp5(
                  id number(10) ,
                  name varchar2(20) constraint emp5_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                  --表级约束
                   constraint emp5_email_uk unique(email),
                   constraint emp5_id_pk primary key(id)
                  )

create table emp6(
                  id number(10) ,
                  name varchar2(20) constraint emp6_name_nn not null,
                  salary number(10,2),
                  email varchar2(20),
                  department_id number(10),
                  --表级约束
                   constraint emp6_email_uk unique(email),
                   constraint emp6_id_pk primary key(id),
                   constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id)
                  )

--级联置空
create table emp7(
                id number(10) ,
                name varchar2(20) constraint emp7_name_nn not null,
                salary number(10,2),
                email varchar2(20),
                department_id number(10),
--表级约束
 constraint emp7_email_uk unique(email),
 constraint emp7_id_pk primary key(id),
 constraint emp7_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
                   )

create table emp8(
                id number(10) ,
                name varchar2(20) constraint emp8_name_nn not null,
                salary number(10,2)constraint emp8_salary check(salary > 1500 and salary < 20000),
                email varchar2(20),
                department_id number(10),
--表级约束
 constraint emp8_email_uk unique(email),
 constraint emp8_id_pk primary key(id),
 constraint emp8_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
                   )

alter table emp5
modify (salary number(11,2) not null)

alter table emp5
drop constraint emp5_name_nn 

alter table emp5
add constraint emp5_name_uk unique(name)

SQL> alter table emp3
  2  disable constraint emp3_email_uk;

SQL> alter table emp3
  2  enable constraint emp3_email_uk;

SELECT constraint_name, constraint_type,
search_condition
FROM 
user_constraints
WHERE 
table_name = 'EMPLOYEES';

SELECT constraint_name, column_name
FROM
user_cons_columns
WHERE 
table_name = 'EMPLOYEES';

SQL基础学习笔记（三）—约束

原文：http://blog.csdn.net/wjw0130/article/details/43531553