【Oracle学习】之 约束

Q：为什么创建索引
A：设置表中的各种约束，以保证数据的一致性

*Topic1：主键约束（PRIMARY KEY） = 唯一性约束（UNIQUE） + 非空约束（NOT NULL）
*Topic2：唯一性约束（UNIQUE）
*Topic3：检查约束（CHECK）
*Topic4：外键约束（FOREIGN KEY）
*Topic5：非空约束（NOT NULL）

一、非空约束

1、在创建表时设置非空约束
语法：CREATE TABLE table_name(column_name datatype NOT NULL,…);
2、在修改表时添加非空约束
语法：ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
注：添加非空约束的字段不能有null数据存在。可先删除表中的数据。DELETE FROMM table_name;
3、在修改表时去除非空约束
语法：ALTER TABLE table_name MODIFY column_name datatype NULL;

//在创建表时设置非空约束 create table userinfo_1(id number(6,0), username varchar2(20) not null, userpwd varchar2(20) not null);desc userinfo_1

//在修改表时添加非空约束 alter table userinfo modify username varchar(20) not null; desc userinfo

//在修改表时去除非空约束alter table userinfo modify username varchar(20) null;desc userinfo

二、主键约束

作用：确保表当中每一行数据的唯一性

（1）创建主键约束

1。一张表只能设计一个主键约束
2。主键约束可以由多个字段构成（联合主键或符合主键）
表级约束：CONSTRAINT constraint_name constraint_type(column_name1,…);
联合主键：CONSTRAINT constraint_name PRIMARY KEY(column_name1,…);
数据字典：USER_CONSTRAINTS 查看定义的约束信息。

//1.在创建表时设置主键约束(列级)语法：CREATE TABLE table_name(column_name datatype PRIMARY KEY,...);//2.在创建表时设置主键约束（表级）语法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT pk_name PRIMARY KEY(column_name1,...)); //pk_name 通常用pk_column1_column2表示//3.数据字典：user_constraints 可以查看某个表中的主键约束信息。desc user_constraints查看约束的名字：select constraint_name from user_constraints where table_name='tbl_name';表名要大写

//1.在创建表时设置主键约束(列级)create table userinfo_p (id number(6,0) primary key, username varchar2(20), userpwd varchar2(20));desc userinfo_p

//2.在创建表时设置主键约束（表级） create table userinfo_p1 (id number(6,0), username varchar2(20), userpwd varchar2(20), constraint pk_id_username primary key(id, username));desc userinfo_p1

//3.查看某个表中的主键约束信息 desc user_constraints select constraint_name from user_constraints where table_name='USERINFO_P1';

x`

（2）修改约束

1. 更改约束的名称：
ALTER TABLE tbl_name RENAME CONSTRAINT old_name TO new_name;
2. 禁用/启用约束：ALTER TABLE tbl_name DISABLE|ENABLE CONSTRAINT constraint_name;
3. 删除约束：ALTER TABLE tbl_name DROP CONSTRAINT constraint_name;
4. 删除主键约束：ALTER TABLE tbl_name DROP PRIMARY KEY [CASCADE];

//修改表时添加主键约束ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1,...);alter table userinfo add constraint pk_id primary key(id);desc userinfoselect constraint_name from user_constraints where table_name='USERINFO';

//更改约束的名字alter table userinfo rename constraint pk_id to new_pk_id;

//禁用/启用约束 alter table userinfo disable constraint new_pk_id; select constraint_name, status from user_constraints where table_name='USERINFO';

//删除约束alter table userinfo drop constraint new_pk_id;select constraint_name, status from user_constraints where table_name='USERINFO';  alter table userinfo_p drop primary key;

三、外键约束

（1）创建外键约束

1、在创建表时设置外键约束（列级）
语法：CREATE TABLE table1(column_name datatype REFERENCES table2(column_name),…); –其中table2为主表，table1为从表
注：（1）设置外键约束时，主表字段必须为该表的主键字段
（2）主从表中相应的字段必须是同一个数据类型
（3）从表中外键字段的值必须来自主表中相应字段的值，或者为null值

//创建表时设置外键约束 create table userinfo_f(id varchar2(10) primary key,username varchar2(20),typeid_new varchar2(10) references typeinfo(typeid));insert into typeinfo values(1, 1);insert into userinfo_f(id, typeid_new)values(1, 1);

//在创建表时设置外键约束create table userinfo_f2(id varchar2(10) primary key,username varchar2(20),typeid_new varchar2(10),constraint fk_typeid_new foreign key(typeid_new)references typeinfo(typeid));create table userinfo_f3(id varchar2(10) primary key,username varchar2(20),typeid_new varchar2(10),constraint fk_typeid_new1 foreign key(typeid_new)references typeinfo(typeid) on delete cascade);

//在修改表时添加外键约束alter table userinfo_f4 add constraint fk_typeid_alter foregin key(typeid_new)references typeinfo(typeid);

（2）删除外键约束

1、禁用外键约束
语法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT fk_name;(enable恢复使用)
2、删除外键约束
语法：ALTER TABLE table_name DROP CONSTRAINT pk_name;
3.查看约束
语法：select constrain_name,constraint_type,status from user_constraints
where table_name=’XXXX’;
status P代表主键 R代表外键

//禁用外键（开启外键 enable）select constraint_name, constraint_type, status from user_constraints where table_name='USERINFO_F4';alter table userinfo_f4 disable constraints FK_TYPEID_ALTER;

//删除外键约束alter table userinfo_f4 drop constraint FK_TYPEID_ALTER;

四、唯一约束

（1）创建唯一约束

1、作用：保证设置唯一约束的字段值的唯一性
2、唯一约束与主键约束的区别：
（1）主键约束的字段为非空的，而唯一约束只允许有一个值为null
（2）一张表只能有一个主键约束，而唯一约束可以有多个

创建表时设置：
列级： column_name data_type UNIQUE,…
表级：CONSTRAINT constraint_name UNIQUE(column_name);

//列级唯一约束create table userinfo_u(id varchar(10) primary key,username varchar2(20) unique,userpwd varchar2(20));

//表级唯一约束create table userinfo_u1(id varchar2(10) primary key,username varchar2(20),constraints un_username unique(username));

（2）添加表唯一约束

在修改表时添加唯一约束
语法：ALTER TABLE table_name ADD CONSTRAINT u_name UNIQUE(column_name);

create table userinfo_u2(id varchar2(10) primary key, username varchar2(20));alter table userinfo_u2 add constraint un_username_new unique(username);

（3）删除唯一约束

//查看约束状态select constraint_name, constraint_type, status from user_constraints where table_name='USERINFO_U2';//禁用/启用唯一约束：disable/enable constraint constraint_name;//删除唯一约束：drop constraint constraint_name

五、检查约束

（1）在创建表时设置检查约束

检查约束
1、作用：让表中的值更具有实际意义。
注：检查约束在一张表中也是可以有多个的。
2、在创建表时设置检查约束（列级）
语法：CREATE TABLE table_name(column_name datatype CHECK(expressions),…);
注：expressions为条件表达式
3、在创建表时设置检查约束（表级）
语法：CREATE TABLE table_name(
column_name datatype,…,
CONSTRAINT c_name CHECK(expressions));

//在创建表时设置检查约束（列级）create table userinfo_c(id varchar2(10) primary key, username varchar2(20),salary number(5,0) check(salary>0));insert into userinfo_c values(1, 'aa', -50);

//在创建表时设置检查约束（表级）create table userinfo_c1(id varchar2(10) primary key, username varchar2(20),salary number(5,0) constraint ck_salary check(salary>0));

（2）在修改表时添加检查约束

修改表时添加检查约束:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK(expressions);

create table userinfo_c3(id varchar2(10) primary key, username varchar2(20),salary number(5,0) );alter table userinfo_c3 add constraint ck_salary_new check(salary > 0);

（3）删除检查约束

5、禁用检查约束
语法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT ck_name;
6、删除检查约束
语法：ALTER TABLE table_name DROP CONSTRAINT ck_name;

//查看select constraint_name, constraint_type, status from user_constraints where table_name='USERINFO_C3';//禁用检查约束alter table userinfo_c3 disable constraint CK_SALARY_NEW;

//删除约束alter table userinfo_c3 drop constraint CK_SALARY_NEW;

---

简单作业关于student 和teacher两表外键