Oracle基础知识点——Oracle约束

Oracle约束

1. 什么是约束

1.1 约束定义
约束： 是强加在表上的规则或条件。确保数据库满足业务规则。保证数据库的完整性。当对表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则的话，系统就会拒绝执行这个操作。约束可以是列一级别的，也可以是表级别的。定义约束时没有给出约束的名字，ORACE系统将为该约束自动生成一个名字，其格式为SYS_Cn，其中n为自然数（强烈建议各位在创建表或增加约束时，给约束定义名称。）

1.2 约束功能
**约束的功能：**实现一些业务规则，防止无效的垃圾数据进入数据库，维护数据库的完整性(完整性指正确性与一致性)。从而使数据库的开发和维护更加容易。

2. 约束分类

约束分为：非空（NOT NULL）约束、唯一（UNIQUE）约束 、主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束、条件（CHECK）约束。

2.1 not null约束
**非空（NOT NULL）约束：**顾名思义，所约束的列不能为NULL值。否则就会报错。

		举例：
		create table user1(id number, name varchar2(30) not null);
		insert into user1 values(001, '');  //会报错

2.2 unique 唯一
**唯一（UNIQUE）约束：**在表中每一行中所定义的这列或者些列的值都不能相同。必须保证唯一性，否则就会违反约束条件。
用于指定列的值不能重复，可以为null

		举例：
			create table user2(id number unique,  name varchar2(30));
			insert into user2 values(1,111);  // id 输入重复的值是会报错的
			注意：Oracle中unique可以为null，而且允许多行为null

2.3 primary key 主键
**主键（PRIMARY KEY）约束：**唯一的标识表中的每一行，不能重复，不能为空。创建主键或唯一约束后，Oracle会自动创建一个与约束同名的索引（UNIQUENES为UNIQUE唯一索引）。需要注意的是：每个表只能有且有一个主键约束。

 	举例：
 		create table user3(id number primary key, name varchar2(30));
 		insert into user3 values(1, 111);
 		insert into user3 values(1, 111);  // 报错，唯一性
 		insert into user3 values(null, 111); //报错，不能为null	
 			
 		特别说明：primary key与unique的区别：
 			1. 一张表可以有多个unique（唯一）约束；
 			2. 一张表只能有一个主键；
 			3. 设置为主键的列不能有null值。	

2.4 foreign key 外键 references
外键（FOREIGN KEY）约束： 用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性，能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库。
用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

 		create table class(id number primary key, name varchar2(32));
 		create table stus(id number primary key,
 									name varchar2(36) not null,
 									classid number references class(id)
 								);
 			特别说明：froelgn key 外键的细节
 			1. 外键指向主键列；
 			2. 外键可以指向unique列；
 			3. 建表时先建主表，再建从表；删除表先删从表，再删主表。
 			4. 外键列属性值要与主键或unique列属性值的类型保持一致。
 			5. 外键列的值，必须在主键列中存在。但外键列的值允许为null

2.5 check 检查
**条件（CHECK）约束：**表中每行都要满足该约束条件。条件约束既可以在表一级定义，也可以在列一级定义。在一列上可以定义任意多个条件约束。

			举例：
				create table user4(id number primary key,
  											sal number check(sal>=1000 and sal<=2000),
  											sex char(2) check(sex in('男', '女'))
  			);
  		
  			insert into user4 values(1, 1000, '男');  // sal列的值不满足1000至2000，报错。
  										

3. 案例：（商店售货系统）（掌握创建约束）

商店售货系统表设计案例：

 		现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
 		商品 goods表：
 				商品号  goodlsId（主键），商品名 goodsName（非空），
 				单价 price（大于零），商品类别category，供应商 provider；

		客户 customer：
				客户号 customerId（主键），姓名 name（非空），住址 address，
				邮箱 email，性别 sex（默认男，check）， 身份证 cardId（唯一）；
		
		购买  purchase：
				客户号 customerId（外键），商品号 goodId（外键），
				购买数量nums（1到30个）;

请用SQL语言完成下列功能：
1. 建表，在定义中要求声明：
（1）每个表的主外键；
（2）客户的姓名不能为空值；
（3）单价必须大于0，购买数量必须在1到30之间；
（4）邮箱不能够重复；
（5）客户的性别必须是 男 或者 女，默认是男；

商品表：
商品 goods表：
商品编号：goodsId 商品名称：goodsName
单价：price 商品类别：category
供应商：provider

	   代码：
	   		create table goods(
						goodId number primary key,
						goodsName varchar2(36),
						price number check(price>0),
						category varchar2(64),
						provider varchar2(64)
			);

客户表：
客户 customer 表：
客户编号：customerId 姓名：name，
住址：address 邮箱：email，
性别：sex 身份证：idCard

	create table customer(
		customerId number primary key, 
		name varchar2(32) not null, 
		address varchar2(64), 
		email varchar2(64) unique, 
		sex char(2) default'男' check(sex in('男','女')), 
		idCard varchar2(20) 
	);

购买表：
购买 purchase 表：
客户编号：customerId 商品编号：goodsId
购买数量：nums

	 代码： 
			create table purchase(
					customerId number references customer(customerId),
					goodsId number references goods(goodsId),
					nums number check(nums>=1 and nums <= 30),
					primary key(customerId, goodsId)
			);

4. 维护约束（掌握）

4.1 修改约束
如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束：
（1）增加not null 约束使用modify（因为字段（列）默认都是可以为空）
（2）增加其它四种约束使用add。

• 添加或修改not null约束

   	语法：
   		alter table 表名 modify 字段名 not null;

• 添加或修改unique(唯一)、primary key(主键)、foreign key(外键) 和 check(检查)约束

      语法：
      		alter table 表名 add constralnt 约束名 约束种类(字段);

• 修改售货系统表设计方案

	1. 增加purchase表主键：
   		alter table purchase add constralnt PK_PURCHASE_PID primary key(cId, gId);
   	2. 客户的姓名不能为空值： --增加商品名也不能为空
   		alter table goods modify goodsName not null; //添加非空约束要用modify
   	3. 邮箱不能够重复：--增加身份证也不能重复
   		alter table customer add coustraint unique_cardId unique(cardId);
   		alter table customer add constraint UK_EMAIL unique(email);
   	4. 增加客户的住址只能是（徐汇区、黄浦区、浦东新区）
   		alter table customer add constraint check_address check(address in('徐汇区', ‘黄浦区’, '浦东新区'));

4.2删除约束
当不再需要某个约束时，可以删除。
删除约束基本语法： alter table 表名 drop constratint 约束名称;
约束名称指的是：一个表的每一个约束都对应一个名称，约束名称用户没有设置时，系统会自动分配一个名称。

特别说明：
			在删除主键约束的时候，可能有错误，
			比如：alter table 表名 drop primary key;
			这是因为如果在两张表存在主从关系，那么在删除主要的主键约束时，必须带上 cascade 选项。
	基本语法：
			alter table 表名 drop primary key cascade;

4.3约束命名规范
约束名称：建议自己定义一套命名规则，否则使用系统生成的约束名，很难能把它和对应的表、字段联系起来。

约束命名规则：
			非空约束：  NN_表名_列名
			唯一约束：	UK_表名_列名
			主键约束：	PK_表名
			外键约束：	FK_表名_列名
			条件约束：	CK_表名_列名

4.4 显示约束信息（了解）
1）显示表约束信息
通过查询数据字典视图 user_constraints，可以显示当前用户所有的约束的信息。

	select constraint_name, constraint_type, status, valldated
			from user_constraints
			where table_name='表名';
			
	注意：表名要大写

2）当然也有更容易的方法，直接用pl/sql developer 查看即可。

4.5 表级定义 列级定义（了解）

• 列级定义
  列级定义：是在定义列的同时定义约束。

		create table department(
					dept_id number(12) constraint pk_department primary key,
					name varchar2(12),
					loc varchar2(12)
		);

• 表级定义
  表级定义：指在定义了所有列后，再定义约束。

 create table employee(emp_id number(4),
 				name varchar2(15),
 				dept_id number(2),
 				constraint pk_employee primary key(emp_id),
 				constraint fk_department foreign key(dept_id) references department4(dept_id)
		);
		特别说明：not null 约束不可以出现在表级定义中，not null约束只能在列级上定义。
			     一般情况下，我们使用列级定义即可。但是如果遇到定义复合主键（两列一起被定义为主键）时，需要用到表级定义。

4.6 联合主键（了解）
举例：将id与name定义为复合主键：

create table test(
			id number,
			name varchar2(64),
			nums number,
			constraint pk_id_name primary key(id,name)
	);
		特别说明：不推荐大家使用复合主键。

本文地址：https://blog.csdn.net/weixin_43170741/article/details/107378462