oracle学习系列之三 （约束）

主键约束；外键约束；唯一性约束；检查约束；默认值约束 -——————五大约束 

一、 主键约束；

--创建表的主键约束
  create table student (student_id number primary key,student_name varchar2(20),sudent_birthday date,student_address varchar2(50),student_phone varchar2(20))

--显示命名主键约束
  --第一种写法：create table student1 (student_id number  constraint pk_student primary key ,student_name varchar2(20),sudent_birthday date,student_address varchar2(50),student_phone varchar2(20))
 第二种写法： create table student1 (student_id number   ,student_name varchar2(20),sudent_birthday date,student_address varchar2(50),student_phone varchar2(20) constraint pk_student primary key(student_id))
 --创建多列主键

create table student1 (student_id number   ,student_name varchar2(20),sudent_birthday date,student_address varchar2(50),student_phone varchar2(20) constraint pk_student primary key(student_id,student_name))
  --查看用户创建的所有约束
  select * from user_constraints where table_name like ‘%STUD%‘

 --创建无主键的表
   create table student2 (student_id number ,student_name varchar2(20),sudent_birthday date,student_address varchar2(50),student_phone varchar2(20))
--为新建的表添加 主键约束
  alter table student2 modify (student_id number primary key )
    --创建无主键的表
   create table student3 (student_id number ,student_name varchar2(20),sudent_birthday date,student_address varchar2(50),student_phone varchar2(20))
  --为表添加多列主键
 alter table student3 add constraint pk_studentForMutPrimarykey primary key (student_name,sudent_birthday,student_address)
 --删除主键 与列一样，主键是表的一个对象，删除表的主键与删除列的语法非常相似。
alter table student3 drop primary key
--当然，如果将主键看做表的一 个对象，而且知道主键的名称，那么可以利用删除约束的语法来删除表的主键。如下：
alter table student3 drop constraint pk_studentForMutPrimarykey

--启用/禁用主键
alter table student disable primary key ;
alter table student enable primary key ;

--重命名主键
alter table student3 rename constraint pk_studentForMutPrimarykey to PK_STUDENT2

主键的应用场景
1. 对于完整性要求比较高的数据表都应该建立主键
2.对于经常按照某列进行查询的数据表，应该考虑建立主键
3.考虑是否对外键有利

二、外键约束；

--创建表customers
create table customers (customer_id number primary key ,customer_name varchar2(50),customer_address varchar2(50),customer_phone varchar2(30),email varchar2(20),constrator varchar2(20));
--创建表orders
create table orders (order_id number primary key ,customer_id number,goods_name varchar(20),quantity number,unit varchar(10));
--创建外键约束
alter table orders add constraint fk_orders_customers foreign key (customer_id) references customers(customer_id)

--重命名外键
alter table orders rename constraint FK_ORDERS_CUSTOMERS to FK_ORDERS
--启用/禁用外键
alter table orders modify constraint FK_ORDERS disable
alter table orders modify constraint FK_ORDERS enable

--删除外键
alter table orders drop constaint FK_ORDERS

三、唯一性约束；

--创建唯一性约束
create table users(user_id number primary key ,
user_name varchar2(50),user_address varchar2(50),user_phone varchar2(20),
email varchar2(20) unique,constractor varchar2(20))
--将表的某一列设置为 ：唯一性约束：
 alter table users add constraint uq_phone unique (user_phone)
 --查看唯一性约束
 select * from user_constraints where table_name=‘USERS‘
 select * from user_cons_columns where table_name=‘USERS‘
--删除唯一性约束
 alter table users drop constraint uq_phone

四、检查约束；

--创建检查约束
create table students (student_id number primary key ,student_name varchar2(10),
subject varchar2(20),score number constraint chk_score check(score between 0 and 100))

--复杂些的检查约束

create table employees(
                           employee_id number primary key ,employee_name varchar2(10),grade varchar2(10),salary number,constraint chk_salary
                           check(
                                       grade in(‘MANAGER‘,‘LENDER‘,‘STAFF‘) --grade 为‘MANAGER‘,‘LENDER‘,‘STAFF‘ 这三个中一个
                                       and
                                            (
                                                       grade=‘MANAGER‘ and salary<=8000    --若grade 为grade=‘MANAGER‘ and salary<=8000
                                                       or grade=‘LERDER‘ and salary<=5000   --若grade 为grade=‘LERDER‘ and salary<=5000
                                                       or grade=‘STAFF‘ and salary<=4000     --若grade=‘STAFF‘ and salary<=4000
                                             )
                                  )
                         )

    --添加检查约束：
alter table employees add constraint chk_name check(length(employee_name)<=4)
--删除检查约束
alter table employees drop constraint chk_name

五、默认值约束