一、维护数据的完整性
概述:数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在Oracle中,数据完整性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现,在这三种方法中,因为约束易于维护,并且具有最好的性能,所以作为维护数据完整性的首选。
A、not null(非空)
如果在列上定义了not null,那么当插入数据时,必须为列提供数据。
B、unique(唯一)
当定义了唯一约束后,该列值是不能重复的,但是可以为null。
C、primary key(主键)
用于唯一的标识表行的数据,当定义主键约束后,该列不但不能重复而且不能为NULL。一张表最多只能有一个主键,但是可以由多个unique约束。
D、foreign key(外键)
用于定义主表和从表之间的关系,外键约束要定义在从表上,主要则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为NULL。
E、check
商品售货系统表设计案例
现在有一个商店的数据库,记录客户及其购物情况,由下面三个表组成:
商品表Goods(商品号GoodsId,商品名GoodName,单价UnitPrice,商品类别Categroy,供应商Provider)
客户表Customers(客户号CustomerId,姓名Name,住址Address,电邮Email,性别Gender,身份证CardId)
销售表Purchases(客户号CustomerId,商品号GoodsId,购买数量Num)
请用SQL语言完成下列功能:
建表,在定义中要求声明:
(1)每个表的主外键。
(2)客户的姓名不能为空值。
(3)单价必须大于0,购买数量必须在1~30之间。
(4)电邮不能够重复。
(5)客户的性别必须是男或女,默认是男。
(1)增加商品名也不能为空
在删除主键约束的时候,可能有错误。比如
通过查询数据字典视图user_constraints,可以显示当前用户所有的约束的信息。
通过查询数据字典视图user_cons_column,可以显示约束所对应的表列信息。
表级定义是指在定义了所有列后,再定义约束,这里需要注意,not null约束只能在列级上定义。
案例:
列级定义是在定义列的同时定义约束。
案例:
概述:数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在Oracle中,数据完整性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现,在这三种方法中,因为约束易于维护,并且具有最好的性能,所以作为维护数据完整性的首选。
1、约束
A、not null(非空)
如果在列上定义了not null,那么当插入数据时,必须为列提供数据。
B、unique(唯一)
当定义了唯一约束后,该列值是不能重复的,但是可以为null。
C、primary key(主键)
用于唯一的标识表行的数据,当定义主键约束后,该列不但不能重复而且不能为NULL。一张表最多只能有一个主键,但是可以由多个unique约束。
D、foreign key(外键)
用于定义主表和从表之间的关系,外键约束要定义在从表上,主要则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为NULL。
E、check
用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间,如果不在1000~2000之间就会提示出错。
商品售货系统表设计案例
现在有一个商店的数据库,记录客户及其购物情况,由下面三个表组成:
商品表Goods(商品号GoodsId,商品名GoodName,单价UnitPrice,商品类别Categroy,供应商Provider)
客户表Customers(客户号CustomerId,姓名Name,住址Address,电邮Email,性别Gender,身份证CardId)
销售表Purchases(客户号CustomerId,商品号GoodsId,购买数量Num)
请用SQL语言完成下列功能:
建表,在定义中要求声明:
(1)每个表的主外键。
(2)客户的姓名不能为空值。
(3)单价必须大于0,购买数量必须在1~30之间。
(4)电邮不能够重复。
(5)客户的性别必须是男或女,默认是男。
CREATE TABLE goods
(
goodsid CHAR(8) PRIMARY KEY,
goodname VARCHAR2(30),
unitprice NUMBER(10, 2) CHECK(unitprice>0),
category VARCHAR2(8),
provider VARCHAR2(30)
);
CREATE TABLE customers
(
customerid CHAR(8) PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
address VARCHAR2(150),
email VARCHAR2(100) UNIQUE,
gender CHAR(2) DEFAULT('男') CHECK(gender IN('男', '女')),
cardid CHAR(18)
);
CREATE TABLE purchases
(
customerid CHAR(8) REFERENCES customers(customerid),
goodsid CHAR(8) REFERENCES goods(goodsid),
num NUMBER(10) CHECK(num BETWEEN 1 AND 30)
);
2、添加约束
(1)增加商品名也不能为空
ALTER TABLE goods
MODIFY goodsid NOT NULL;
(2)增加身份证也不能重复
ALTER TABLE customers
ADD CONSTRAINT uq_cardid UNIQUE(cardid);
(3)增加客户的住址只能是海淀、朝阳、东城、西域
ALTER TABLE customers
ADD CONSTRAINT ck_address CHECK(address IN ('海淀','朝阳','东城','西域'));
3、删除约束
alter table 表名 drop constraint 约束名称;
特别说明:
在删除主键约束的时候,可能有错误。比如
alter table 表名 drop primary key;
这是因为如果在两表存在主从关系,那么在删除主表主键约束时,必须带上cascade选项。
alter table 表名 drop primary key cascade;
4、显示约束信息
通过查询数据字典视图user_constraints,可以显示当前用户所有的约束的信息。
SELECT constraint_name,
constraint_type,
status,
validated
FROM user_constraints
WHERE table_name = 'Goods';
B、显示列约束
通过查询数据字典视图user_cons_column,可以显示约束所对应的表列信息。
SELECT column_name,
position
FROM user_cons_columns
WHERE constraint_name = 'CK_Address';
5、表级定义和列级定义
表级定义是指在定义了所有列后,再定义约束,这里需要注意,not null约束只能在列级上定义。
案例:
CREATE TABLE goods
(
goodsid CHAR(8),
goodsname VARCHAR2(50),
category VARCHAR(30),
CONSTRAINT pk_goodsid PRIMARY KEY(goodsid)
);
B、列级定义
列级定义是在定义列的同时定义约束。
案例:
CREATE TABLE goods
(
goodsid CHAR(8) CONSTRAINT pk_goodsid PRIMARY KEY,
goodname VARCHAR2(50),
unitprice NUMBER(10, 2) CHECK(unitprice>0),
category VARCHAR(30),
provider VARCHAR(100)
);