Chapter 04-表及完整性约束-

1、表、完整性约束1本章要点 管理表 完整性约束的使用2数据库对象的命名规则必须以字母开头可包括数字和三个特殊字符（# _ $）不要使用oracle的保留字同一用户下的对象不能同名3数据类型数据类型说明varchar2(size)可变长字符串，最大4000字符char(size) 定长字符串，最大2000字符number(p,s) 数字值，可以表示整数，也可表示小数，p表有效数位数date 日期值alter session set nls_date_format=yyyy-mm-dd; （修改默认日期格式） 4建表的语句为了创建表，你必须具有：创建表的权限足够的存储区域标准的建表语法：CREATE

2、 TABLE schema.table (column datatype DEFAULT expr , ); select * from session_privs如何判断一个用户有没有 创建表的权限？5CREATE TABLE 表名称( 字段名称1 字段类型 DEFAULT 默认值, 字段名称1 字段类型 DEFAULT 默认值, 字段名称n 字段类型 DEFAULT 默认值)6例：创建一张 person 表，表中的字段类型 如下所示：NO.字段名称字段类型描述1pidVARCHAR2(18)表示人员编号2nameVARCHAR2(200)表示人员的姓名3ageNUMBER(3)表示人员的年

3、龄4birthdayDATE表示人员的生日5sexVARCHAR2(2)表示人员的性别，默认值是“男”7create table person( pid varchar2(18) , name varchar2(200) , age number(3) , birthday date , sex varchar2(2) default 男);8例：向表中增加数据insert into person(pid,name,age,birthday,sex) VALUES(11111111111111111,张三,30,TO_DATE(1976-02-13,yyyy-mm-dd),女) ;insert

4、 into person(pid,name,age,birthday) VALUES(22222222222222222,李四,31,TO_DATE(1986-02-13,yyyy-mm-dd) ;insert into person(pid,name,age,birthday,sex) VALUES(33333333333333333,王五,32,TO_DATE(1977-08-20,yyyy-mm-dd),女) ;insert into person(pid,name,age,birthday) VALUES(44444444444444444,钱七,33,TO_DATE(1986-02-

5、13,yyyy-mm-dd) ;9使用子查询创建表使用子查询创建表的语法CREATE TABLE table column(, column.)AS subquery;新表的字段列表必须与子查询中的字段列表匹配字段列表可以省略 示例create table emp_copy as select * from emp where 1=2create table emp_copy as select * from emp 提问：比较这两条语句有什么区别？10例：为person表增加一列address，脚本如下：方法一：先删除表：drop table person;再创建表：create table

6、 person( pid varchar2(18) , name varchar2 (200) , age number(3) , birthday date , sex varchar2 (2) default 男, address varchar2 (200) );11 ALTER TABLE 语句方法二：使用alter table 语句可以:增加字段修改字段删除字段ALTER TABLE tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype D

7、EFAULT expr , column datatype.);ALTER TABLE tableDROP (columns);12在表中增加字段使用 ADD 子句增加字段，新的字段只能被加到整个表的最后。语法：ALTER TABLE 表名称 ADD (列的名称 列的类型 DEFAULT 默认值 , 列的名称 列的类型 DEFAULT 默认值,);例：为 person 表中增加 address 列alter table person add address varchar2 (200) default 暂无地址; 13修改字段可修改列的数据类型，大小和默认值修改表结构的语法：ALTER TAB

8、LE 表名称 MODIFY (列的名称 列的类型 DEFAULT 默认值)例：修改 person 表中的 name 列，加入默认值，修改大小ALTER TABLE person MODIFY name VARCHAR2(20) DEFAULT 无名氏;不是任何情况都可以修改的，当字段只包含空值时，类型、大小都可以修改;在修改表结构的时候，如果假设数据库中对应的字段里有一个很长的数据，则无法将表的长度缩小，例如：现在在 name 字段中存在一个长度为 20 个字符的字母，原本的 name 可以容纳 200 个内容，但是此时要将 name 字段的长度修改为 10，则肯定无法实现。14建议： 在一般

9、的数据库程序开发中，很少去修改表结构，这一点在IBM DB2数据库中就没有提供ALERT TABLE命令，所以在建表的时候一般要考虑到位，一般都是事先为各类型字段建立几个备用字段，这些备用字段默认值都是允许为空，以便在不使用这些备用字段时出现错误。15删除字段可以从表中删除列: ALTER TABLE employees DROP COLUMN sex;从每行中删除掉字段占据的长度和数据，释放在数据块中占用的空间。删除大表中的字段将需要比较长的时间。16删除表的内容TRUNCATE TABLE 语句清除表中所有的记录是DDL语句，不可以回滚释放表的存储空间是删除数据的方法之一TRUNCATE

10、TABLE table_name;17删除表表中所有数据将被删除没有完成的事务被提交所有相关的索引被删除不能回退语法：DROP TABLE table_name;18改变对象名称使用RENAME语句改变对象名称，可以修改表、视图、序列或者同义词的名称RENAME old_name TO new_name;必须是对象的所有者19数据完整性分类：域完整性；（控制一列的约束条件）实现CHECK 实体完整性；（一行有唯一标识）PRIMARY KEY参照完整性。（主表和从表的数据一致性）FOREIGN KEY20什么是约束约束是在表上强制执行的数据校验规则.当表中数据有相互依赖性时，可以保护相关的数据不

11、被删除.Oracle 支持下面五类完整性约束:NOT NULL 非空UNIQUE KEY 唯一键PRIMARY KEY 主键FOREIGN KEY 外键CHECK 检查21约束概述Oracle使用SYS_Cxxx格式命名约束，也可以由用户命名创建约束的时机:在建表的同时创建（首选）建表后创建可定义列级或表级约束有单列约束和多列约束22主键约束（ PRIMARY KEY） 主键从功能上看相当于非空且唯一 一个表中只允许一个主键 主键是表中能够唯一确定一个行数据的字段 主键字段可以是单字段或者是多字段的组合 Oracle为主键创建对应的唯一性索引23创建表时创建约束CREATE TABLE sch

12、ema.table (column datatype DEFAULT exprcolumn_constraint,table_constraint);CREATE TABLE emp( emp_id NUMBER(3), name VARCHAR2(16), dept_id NUMBER(4) NOT NULL, CONSTRAINT emp_empid_pk PRIMARY KEY (emp_id);24主键约束（ PRIMARY KEY）CREATE TABLE departments( department_id NUMBER(4), department_name VARCHAR2(3

13、0) CONSTRAINT dept_name_nn NOT NULL, manager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id);25例：建立 person 表，在 pid 上增加主键约束CREATE TABLE person( pid VARCHAR2(18) PRIMARY KEY, name VARCHAR2(200) , age NUMBER(3) , birthday DATE , sex VARCHAR2(2) DEFAULT 男);例：插入数据，其中

14、插入的主键重复和为空26执行范例后，会给出错误的提示信息，但是这些约束属于系统自动分配好的约束名称，也可以通过使用CONSTRAINT指定一个约束的名字。例：将person表中的pid指定为主键，并指定主键名称CREATE TABLE person( pid VARCHAR2(18) , name VARCHAR2(200) , age NUMBER(3) , birthday DATE , sex VARCHAR2(2) DEFAULT 男 , CONSTRAINT person_pid_pk PRIMARY KEY(pid);27CREATE TABLE employees( employ

15、ee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email);外键约束

16、（ FOREIGN KEY）28例：要求完成一个程序，一本书要属于一个人 书本身应该是一张表，一个书中必然有一个字段表示属于那个人的。 DROP TABLE person ;DROP TABLE book ;CREATE TABLE person( pid VARCHAR2(18) , name VARCHAR2(200) , age NUMBER(3) , birthday DATE, sex VARCHAR2(2) , CONSTRAINT person_pid_pk PRIMARY KEY(pid) , CONSTRAINT person_name_uk UNIQUE(name) , C

17、ONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) , CONSTRAINT person_sex_ck CHECK(sex IN (男,女,中);29CREATE TABLE book( bid NUMBER PRIMARY KEY NOT NULL , bname VARCHAR(30), bprice NUMBER(5,2), pid VARCHAR(18);30 创建完两张表之后，下一步插入一些测试数据：INSERT INTO person(pid,name,age,birthday,sex) VALUES(111111111111

18、11111,张三,30,TO_DATE(1976-02-13,yyyy-mm-dd),女) ;INSERT INTO book(bid,bname,bprice,pid) VALUES(1,JAVA SE,89.9,11111111111111111) ;确实可以符合要求，但是如果现在插入了以下的数据：INSERT INTO book(bid,bname,bprice,pid) VALUES(2,JAVA SE,89.9,000000) ; 此编号的人员并不存在，如果不存在，则此数据肯定不应该插入。此时，如果要想解决这样的问题，则肯定要使用主外键关联，关联之后子表的数据要跟随父表的数据内容。3

19、1修改book表增加主外键约束CREATE TABLE book( bid NUMBER PRIMARY KEY NOT NULL , bname VARCHAR(30), bprice NUMBER(5,2), pid VARCHAR(18), CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid);32说明：此时，可以保证两张表的数据的完整性，不会出现找不到对应数据的情况。在使用主-外键关联的时候也要有以下的注意点； 在子表中设置的外键在父表中必须是主键或是主表的唯一性约束的字段 删除时应该先删除子表，再删

20、除父表 DROP TABLE person ; DROP TABLE book ;则会出现错误信息，应改为如下删除方式： DROP TABLE book ; DROP TABLE person;33可以使用强制性的删除手段： DROP TABLE person CASCADE CONSTRAINT ;不管约束，而直接删除，但是这种做法一般不建议使用，因为会产生垃圾数据。34 如果删除person表中的一条数据时（如果该数据与book表中的一条记录相关联）则删除无法成功，因为在子表中存在此项的关联，所以无法删除，那么，此时如果要想完成删除操作的话，则必须先将 book 表中对应的数据删除掉。 如

21、果，希望一个表中的数据在删除时，可以自动删除掉其对应的子表记录，则就可以使用级联删除的操作。35 DROP TABLE person ;DROP TABLE book ;CREATE TABLE person( pid VARCHAR2(18) , name VARCHAR2(200) , age NUMBER(3) , birthday DATE, sex VARCHAR2(2) , CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,CONSTRAINT person_name_uk UNIQUE(name) ,CONSTRAINT person_age_

22、ck CHECK(age BETWEEN 0 AND 150) ,CONSTRAINT person_sex_ck CHECK(sex IN (男,女,中);36CREATE TABLE book( bid NUMBER PRIMARY KEY NOT NULL , bname VARCHAR(30), bprice NUMBER(5,2), pid VARCHAR(18), CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE);INSERT INTO person(pi

23、d,name,age,birthday,sex) VALUES(11111111111111111,张三,30,TO_DATE(1976-02-13,yyyy-mm-dd),女) ;INSERT INTO book(bid,bname,bprice,pid) VALUES(1,JAVA SE,89.9,11111111111111111) ;再执行删除“张三”，能够将“张三”对应的书籍一并删除。37确保字段值不允许为空只能在字段级定义非空约束（NOT NULL）38非空约束（NOT NULL）CREATE TABLE employees( employee_id NUMBER(6), last

24、_name VARCHAR2(25) NOT NULL , salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL)39唯一性约束（UNIQUE） 唯一性约束条件确保所在的字段或者字段组合不 出现重复值 唯一性约束条件的字段允许出现空值 Oracle将为唯一性约束条件创建对应的唯一性索 引40唯一性约束（UNIQUE）CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25

25、) NOT NULL, email VARCHAR2(25) , salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, CONSTRAINT emp_email_uk UNIQUE(email);41例：要求姓名字段不允许出现重名。CREATE TABLE person( pid VARCHAR2(18) PRIMARY KEY, name VARCHAR2(200) UNIQUE NOT NULL, age NUMBER(3) , birthday DATE , sex VARCHAR2(2) DEF

26、AULT 男);测试插入两个姓名相同的人。42要想指定约束的名称，也可以通过 CONSTRAINT关键字进行命名：CREATE TABLE person( pid VARCHAR2(18), name VARCHAR2(200) NOT NULL, age NUMBER(3) NOT NULL, birthday DATE , sex VARCHAR2(2) DEFAULT 男, CONSTRAINT person_pid_pk PRIMARY KEY(pid) , CONSTRAINT person_name_uk UNIQUE(name);43检查（CHECK） 约束定义在字段上的每一记录

27、都要满足的条件在check中定义检查的条件表达式，数据需要符合设置的条件条件表达式不允许使用: SYSDATE, UID, USER, USERENV 等函数参照其他记录的值., salaryNUMBER(2) CONSTRAINT emp_salary_min CHECK (salary 0),.44例：设定年龄、性别的取值范围CREATE TABLE person( pid VARCHAR2(18) PRIMARY KEY, name VARCHAR2(200) UNIQUE NOT NULL, age NUMBER(3) NOT NULL CHECK(age BETWEEN 0 AND

28、100), birthday DATE , sex VARCHAR2(2) DEFAULT 男 CHECK(sex IN(男, 女, 中);45采用CONTRAINT实现自定义检查约束命名 CREATE TABLE person( pid VARCHAR2(18) , name VARCHAR2(200) , age NUMBER(3) , birthday DATE, sex VARCHAR2(2) , CONSTRAINT person_pid_pk PRIMARY KEY(pid) , CONSTRAINT person_name_uk UNIQUE(name) , CONSTRAINT

29、 person_age_ck CHECK(age BETWEEN 0 AND 150) , CONSTRAINT person_sex_ck CHECK(sex IN (男,女,中);46外键是构建于一个表的两个字段或者两个表的两个字段之间的关系外键确保了相关的两个字段的两个关系：子表外键列的值必须在主表参照列值的范围内或者为空主表外键值被子表参照时，主表记录不允许被删除外键参照的是主表的主键或者唯一键外键约束（ FOREIGN KEY）47增加约束很少使用可增加或删除约束，但不能直接修改可使约束启用和禁用非空约束必须使用MODIFY子句增加ALTER TABLE table ADD CONSTRAINT constraint type (column);48 添加约束的语法如下：ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段