Chapter 04-表及完整性約束-

1、表、完整性約束1本章要點(diǎn) 管理表 完整性約束的使用2數(shù)據(jù)庫(kù)對(duì)象的命名規(guī)則必須以字母開頭可包括數(shù)字和三個(gè)特殊字符（# _ $）不要使用oracle的保留字同一用戶下的對(duì)象不能同名3數(shù)據(jù)類型數(shù)據(jù)類型說(shuō)明varchar2(size)可變長(zhǎng)字符串，最大4000字符char(size) 定長(zhǎng)字符串，最大2000字符number(p,s) 數(shù)字值，可以表示整數(shù)，也可表示小數(shù)，p表有效數(shù)位數(shù)date 日期值alter session set nls_date_format=yyyy-mm-dd; （修改默認(rèn)日期格式） 4建表的語(yǔ)句為了創(chuàng)建表，你必須具有：創(chuàng)建表的權(quán)限足夠的存儲(chǔ)區(qū)域標(biāo)準(zhǔn)的建表語(yǔ)法：CREATE

2、 TABLE schema.table (column datatype DEFAULT expr , ); select * from session_privs如何判斷一個(gè)用戶有沒(méi)有 創(chuàng)建表的權(quán)限？5CREATE TABLE 表名稱( 字段名稱1 字段類型 DEFAULT 默認(rèn)值, 字段名稱1 字段類型 DEFAULT 默認(rèn)值, 字段名稱n 字段類型 DEFAULT 默認(rèn)值)6例：創(chuàng)建一張 person 表，表中的字段類型 如下所示：NO.字段名稱字段類型描述1pidVARCHAR2(18)表示人員編號(hào)2nameVARCHAR2(200)表示人員的姓名3ageNUMBER(3)表示人員的年

3、齡4birthdayDATE表示人員的生日5sexVARCHAR2(2)表示人員的性別，默認(rèn)值是“男”7create table person( pid varchar2(18) , name varchar2(200) , age number(3) , birthday date , sex varchar2(2) default 男);8例：向表中增加數(shù)據(jù)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使用子查詢創(chuàng)建表使用子查詢創(chuàng)建表的語(yǔ)法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 提問(wèn)：比較這兩條語(yǔ)句有什么區(qū)別？10例：為person表增加一列address，腳本如下：方法一：先刪除表：drop table person;再創(chuàng)建表：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 語(yǔ)句方法二：使用alter table 語(yǔ)句可以:增加字段修改字段刪除字段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 子句增加字段，新的字段只能被加到整個(gè)表的最后。語(yǔ)法：ALTER TABLE 表名稱 ADD (列的名稱 列的類型 DEFAULT 默認(rèn)值 , 列的名稱 列的類型 DEFAULT 默認(rèn)值,);例：為 person 表中增加 address 列alter table person add address varchar2 (200) default 暫無(wú)地址; 13修改字段可修改列的數(shù)據(jù)類型，大小和默認(rèn)值修改表結(jié)構(gòu)的語(yǔ)法：ALTER TAB

8、LE 表名稱 MODIFY (列的名稱 列的類型 DEFAULT 默認(rèn)值)例：修改 person 表中的 name 列，加入默認(rèn)值，修改大小ALTER TABLE person MODIFY name VARCHAR2(20) DEFAULT 無(wú)名氏;不是任何情況都可以修改的，當(dāng)字段只包含空值時(shí)，類型、大小都可以修改;在修改表結(jié)構(gòu)的時(shí)候，如果假設(shè)數(shù)據(jù)庫(kù)中對(duì)應(yīng)的字段里有一個(gè)很長(zhǎng)的數(shù)據(jù)，則無(wú)法將表的長(zhǎng)度縮小，例如：現(xiàn)在在 name 字段中存在一個(gè)長(zhǎng)度為 20 個(gè)字符的字母，原本的 name 可以容納 200 個(gè)內(nèi)容，但是此時(shí)要將 name 字段的長(zhǎng)度修改為 10，則肯定無(wú)法實(shí)現(xiàn)。14建議： 在一般

9、的數(shù)據(jù)庫(kù)程序開發(fā)中，很少去修改表結(jié)構(gòu)，這一點(diǎn)在IBM DB2數(shù)據(jù)庫(kù)中就沒(méi)有提供ALERT TABLE命令，所以在建表的時(shí)候一般要考慮到位，一般都是事先為各類型字段建立幾個(gè)備用字段，這些備用字段默認(rèn)值都是允許為空，以便在不使用這些備用字段時(shí)出現(xiàn)錯(cuò)誤。15刪除字段可以從表中刪除列: ALTER TABLE employees DROP COLUMN sex;從每行中刪除掉字段占據(jù)的長(zhǎng)度和數(shù)據(jù)，釋放在數(shù)據(jù)塊中占用的空間。刪除大表中的字段將需要比較長(zhǎng)的時(shí)間。16刪除表的內(nèi)容TRUNCATE TABLE 語(yǔ)句清除表中所有的記錄是DDL語(yǔ)句，不可以回滾釋放表的存儲(chǔ)空間是刪除數(shù)據(jù)的方法之一TRUNCATE

10、TABLE table_name;17刪除表表中所有數(shù)據(jù)將被刪除沒(méi)有完成的事務(wù)被提交所有相關(guān)的索引被刪除不能回退語(yǔ)法：DROP TABLE table_name;18改變對(duì)象名稱使用RENAME語(yǔ)句改變對(duì)象名稱，可以修改表、視圖、序列或者同義詞的名稱RENAME old_name TO new_name;必須是對(duì)象的所有者19數(shù)據(jù)完整性分類：域完整性；（控制一列的約束條件）實(shí)現(xiàn)CHECK 實(shí)體完整性；（一行有唯一標(biāo)識(shí)）PRIMARY KEY參照完整性。（主表和從表的數(shù)據(jù)一致性）FOREIGN KEY20什么是約束約束是在表上強(qiáng)制執(zhí)行的數(shù)據(jù)校驗(yàn)規(guī)則.當(dāng)表中數(shù)據(jù)有相互依賴性時(shí)，可以保護(hù)相關(guān)的數(shù)據(jù)不

11、被刪除.Oracle 支持下面五類完整性約束:NOT NULL 非空UNIQUE KEY 唯一鍵PRIMARY KEY 主鍵FOREIGN KEY 外鍵CHECK 檢查21約束概述Oracle使用SYS_Cxxx格式命名約束，也可以由用戶命名創(chuàng)建約束的時(shí)機(jī):在建表的同時(shí)創(chuàng)建（首選）建表后創(chuàng)建可定義列級(jí)或表級(jí)約束有單列約束和多列約束22主鍵約束（ PRIMARY KEY） 主鍵從功能上看相當(dāng)于非空且唯一 一個(gè)表中只允許一個(gè)主鍵 主鍵是表中能夠唯一確定一個(gè)行數(shù)據(jù)的字段 主鍵字段可以是單字段或者是多字段的組合 Oracle為主鍵創(chuàng)建對(duì)應(yīng)的唯一性索引23創(chuàng)建表時(shí)創(chuàng)建約束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 男);例：插入數(shù)據(jù)，其中

14、插入的主鍵重復(fù)和為空26執(zhí)行范例后，會(huì)給出錯(cuò)誤的提示信息，但是這些約束屬于系統(tǒng)自動(dòng)分配好的約束名稱，也可以通過(guò)使用CONSTRAINT指定一個(gè)約束的名字。例：將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例：要求完成一個(gè)程序，一本書要屬于一個(gè)人 書本身應(yīng)該是一張表，一個(gè)書中必然有一個(gè)字段表示屬于那個(gè)人的。 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 創(chuàng)建完兩張表之后，下一步插入一些測(cè)試數(shù)據(jù)：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) ;確實(shí)可以符合要求，但是如果現(xiàn)在插入了以下的數(shù)據(jù)：INSERT INTO book(bid,bname,bprice,pid) VALUES(2,JAVA SE,89.9,000000) ; 此編號(hào)的人員并不存在，如果不存在，則此數(shù)據(jù)肯定不應(yīng)該插入。此時(shí)，如果要想解決這樣的問(wèn)題，則肯定要使用主外鍵關(guān)聯(lián)，關(guān)聯(lián)之后子表的數(shù)據(jù)要跟隨父表的數(shù)據(jù)內(nèi)容。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說(shuō)明：此時(shí)，可以保證兩張表的數(shù)據(jù)的完整性，不會(huì)出現(xiàn)找不到對(duì)應(yīng)數(shù)據(jù)的情況。在使用主-外鍵關(guān)聯(lián)的時(shí)候也要有以下的注意點(diǎn)； 在子表中設(shè)置的外鍵在父表中必須是主鍵或是主表的唯一性約束的字段 刪除時(shí)應(yīng)該先刪除子表，再刪

20、除父表 DROP TABLE person ; DROP TABLE book ;則會(huì)出現(xiàn)錯(cuò)誤信息，應(yīng)改為如下刪除方式： DROP TABLE book ; DROP TABLE person;33可以使用強(qiáng)制性的刪除手段： DROP TABLE person CASCADE CONSTRAINT ;不管約束，而直接刪除，但是這種做法一般不建議使用，因?yàn)闀?huì)產(chǎn)生垃圾數(shù)據(jù)。34 如果刪除person表中的一條數(shù)據(jù)時(shí)（如果該數(shù)據(jù)與book表中的一條記錄相關(guān)聯(lián)）則刪除無(wú)法成功，因?yàn)樵谧颖碇写嬖诖隧?xiàng)的關(guān)聯(lián)，所以無(wú)法刪除，那么，此時(shí)如果要想完成刪除操作的話，則必須先將 book 表中對(duì)應(yīng)的數(shù)據(jù)刪除掉。 如

21、果，希望一個(gè)表中的數(shù)據(jù)在刪除時(shí)，可以自動(dòng)刪除掉其對(duì)應(yīng)的子表記錄，則就可以使用級(jí)聯(lián)刪除的操作。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) ;再執(zhí)行刪除“張三”，能夠?qū)ⅰ皬埲睂?duì)應(yīng)的書籍一并刪除。37確保字段值不允許為空只能在字段級(jí)定義非空約束（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） 唯一性約束條件確保所在的字段或者字段組合不 出現(xiàn)重復(fù)值 唯一性約束條件的字段允許出現(xiàn)空值 Oracle將為唯一性約束條件創(chuàng)建對(duì)應(yīng)的唯一性索 引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例：要求姓名字段不允許出現(xiàn)重名。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 男);測(cè)試插入兩個(gè)姓名相同的人。42要想指定約束的名稱，也可以通過(guò) CONSTRAINT關(guān)鍵字進(jìn)行命名：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中定義檢查的條件表達(dá)式，數(shù)據(jù)需要符合設(shè)置的條件條件表達(dá)式不允許使用: SYSDATE, UID, USER, USERENV 等函數(shù)參照其他記錄的值., salaryNUMBER(2) CONSTRAINT emp_salary_min CHECK (salary 0),.44例：設(shè)定年齡、性別的取值范圍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實(shí)現(xiàn)自定義檢查約束命名 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外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或者兩個(gè)表的兩個(gè)字段之間的關(guān)系外鍵確保了相關(guān)的兩個(gè)字段的兩個(gè)關(guān)系：子表外鍵列的值必須在主表參照列值的范圍內(nèi)或者為空主表外鍵值被子表參照時(shí)，主表記錄不允許被刪除外鍵參照的是主表的主鍵或者唯一鍵外鍵約束（ FOREIGN KEY）47增加約束很少使用可增加或刪除約束，但不能直接修改可使約束啟用和禁用非空約束必須使用MODIFY子句增加ALTER TABLE table ADD CONSTRAINT constraint type (column);48 添加約束的語(yǔ)法如下：ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 約束類型(約束字段