oracle 5種約束條件使用方法

1、1 約束約束是操作表數(shù)據(jù)的強(qiáng)制規(guī)定有以下五種約束: NOT NULL ;非空約束 UNIQUE ：唯一值約束 PRIMARY KEY：主鍵約束 FOREIGN KEY：外鍵約束 CHECK：檢查約束約束：建表的時(shí)候可以加約束，建表后也可加約束注意事項(xiàng)、如果不指定約束名 Oracle server 自動(dòng)按照 S、YS_Cn 的格式指定約束名、在什么時(shí)候創(chuàng)建約束: 建表的同時(shí) 建表之后、可以在表級(jí)或列級(jí)定義約束、可以通過(guò)數(shù)據(jù)字典視圖查看約束create table newdeptas select * from departmentscreate table newempas select * f

2、rom employees2 定義約束CREATE TABLE schema.table (column datatype DEFAULT exprcolumn_constraint,.table_constraint,.);CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), . job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);列級(jí)column CONSTRAINT constraint_n

3、ame constraint_type, 表級(jí)column,. CONSTRAINT constraint_name constraint_type (column, .),3 NOT NULL 約束只能定義在列級(jí):保證列值不能為空:修改first_name 非空alter table newempmodify (first_name NOT NULL)取消非空約束alter table newempmodify (first_name NULL)增加約束定義每一行必須滿(mǎn)足的條件以下的表達(dá)式是不允許的: 出現(xiàn)CURRVAL, NEXTVAL, LEVEL, 和ROWNUM 偽列 使用 SYSD

4、ATE, UID, USER, 和 USERENV 函數(shù) 在查詢(xún)中涉及到其它列的值., salaryNUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),.限制工資不能少于2000alter table newempADD constraints newemp_salary_ck CHECK(salary>=2000)添加約束的語(yǔ)法使用 ALTER TABLE 語(yǔ)句:添加或刪除約束, 但是不能修改約束有效化或無(wú)效化約束添加 NOT NULL 約束要使用 MODIFY 語(yǔ)句 ALTER TABLE tableADD CONSTR

5、AINT constraint type (column);添加約束 ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);alter table 表名modify （自定義列名 約束設(shè)置）CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2

6、,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,4 主鍵約束限制列值不允許空值，且不能為空，一個(gè)表只有一個(gè)主鍵只要主鍵都會(huì)Alter，一般主鍵是ID唯一的alter table newempADD constraints newemp_empid_pk primary key(employee_id)刪除主鍵約束alter table newempdrop constraints newemp_empid_pk;復(fù)合主鍵 （多個(gè)列設(shè)置為主鍵）ALter table newempadd constraint newemp_enam

7、e_pk primary key(first_name, last_name)5 唯一約束唯一值約束，限制列值不允許重復(fù)，可以為空（不會(huì)限制空值），一個(gè)表可以有多個(gè)唯一值的約束，不允許insert into可以定義在表級(jí)或列級(jí):設(shè)置唯一值約束alter table newempADD constraints newemp_email_uk UNIQUE(email) CREATE TABLE departments( department_id NUMBER(4), department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, m

8、anager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id);查約束，限制自定義條件，忽略空值可以定義在表級(jí)或列級(jí): CREATE TABLE employees( employee_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,. CONS

9、TRAINT emp_email_uk UNIQUE(email);6 外鍵約束一般都是主鍵的ID 不能修改EMPLOYEESINSERT INTO允許PRIMARYKEY可以定義在表級(jí)或列級(jí):FOREIGN KEY: 在表級(jí)指定子表中的列REFERENCES: 標(biāo)示在父表中的列，后面跟一樣的主鍵ON DELETE CASCADE: 當(dāng)父表中的列被刪除是，子表中相對(duì)應(yīng)的列也被刪除ON DELETE SET NULL: 子表中相應(yīng)的列置空CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL,

10、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);主外鍵關(guān)聯(lián)外鍵約束:限制外鍵列值必須是引用主鍵中的主鍵列存在的列值，不限制空值A(chǔ)Lter table ne

11、wdeptadd constraint newdeptid primary key(department_id) alter table newemp部門(mén)主鍵約束設(shè)置ADD CONSTRAINTS newemp_deptid_fk FOREIGN KEY(department_id)/ newemp_deptid_fk鼠標(biāo)點(diǎn)擊名稱(chēng)references newdept (department_ID);/關(guān)聯(lián)想要給newemp增加外鍵的前提是newdept必須有主鍵，先設(shè)置newdept的主鍵之后才會(huì)有外鍵7級(jí)聯(lián)刪除刪除約束從表 EMPLOYEES 中刪除約束 ALTER TABLE employ

12、eesDROP CONSTRAINT emp_manager_fk;使用CASCADE選項(xiàng)刪除約束外鍵和主鍵一起刪除在ALTER TABLE 語(yǔ)句中使用 DISABLE 子句將約束無(wú)效化。使用 CASCADE 選項(xiàng)將相關(guān)的約束也無(wú)效化ALTER TABLEemployeesDISABLE CONSTRAINTemp_emp_id_pk CASCADE;/中間的是約束的名字ALTER TABLEdepartmentsDROP PRIMARY KEY CASCADE;alter table newempadd constraints newemp_deptid_fk foreign key(dep

13、artment_id)references newdept(department_id)/REFERENCES 關(guān)聯(lián)的是父鍵dept所以是department_idon delete cascade級(jí)聯(lián)置空員工不刪除但是把外鍵刪除alter table newempadd constraints newemp_deptid_fk foreign key(department_id)references newdept(department_id)on delete set null/引用之前必須drop掉外鍵在一塊執(zhí)行ALTER TABLE newemp ADD CONSTRAINTS new

14、emp_deptid_fk FOREIGN KEY(department_id) REFERENCES newdept(department_id) ON DELETE CASCADE;/必須刪除外鍵之后才能執(zhí)行語(yǔ)句ALTER TABLE newemp ADD CONSTRAINTS newemp_deptid_fk FOREIGN KEY(department_id) REFERENCES newdept(department_id) ON DELETE SET NULL; /想要?jiǎng)h除不能按鈕drop要用下面的語(yǔ)句刪除外鍵 alter table newemp drop CONSTRAINT

15、S newemp_deptid_fk;8 激活約束在ALTER TABLE 語(yǔ)句中使用 DISABLE 子句將約束無(wú)效化。使用 CASCADE 選項(xiàng)將相關(guān)的約束也無(wú)效化ENABLE 子句可將當(dāng)前無(wú)效的約束激活 ALTER TABLEemployeesENABLE CONSTRAINTemp_emp_id_pk;Table altered.當(dāng)定義或激活UNIQUE 或 PRIMARY KEY 約束時(shí)系統(tǒng)會(huì)自動(dòng)創(chuàng)建UNIQUE 或 PRIMARY KEY索引無(wú)效化約束Alter table newempdisable constraint newemp_ename_pk激活約束Alter tabl

16、e newempenable constraint newemp_ename_pk9 查詢(xún)約束查詢(xún)數(shù)據(jù)字典視圖 USER_CONSTRAINTSSELECTconstraint_name, constraint_type,search_conditionFROMuser_constraintsWHEREtable_name = 'EMPLOYEES'查詢(xún)定義約束的列SELECTconstraint_name, column_nameFROMuser_cons_columnsWHEREtable_name = 'EMPLOYEES'10 建表的時(shí)候增加約束CREA

17、TE table emp2(emp_id number(10) primary key, 設(shè)置主鍵emp_name varchar2(60) NOT NULL, 非空約束emp_email varchar2(50) UNIQUE, 唯一值約束emp_sal number(8,2) check(emp_sal>=500),檢查約束emp_deptid number(6) references newdept (department_id) 外鍵約束constraints emp2_emp2_uk unique(emp_email) /,沒(méi)有完成要加， 強(qiáng)制約束條件同primary key1

18、1 索引:主鍵唯一的 ，索引方便快速的查詢(xún)，關(guān)聯(lián)的機(jī)構(gòu)，加快速度，目錄索引方便地位查找，給小范圍尋找索引還會(huì)往下查作用:加速查詢(xún)，但是會(huì)降低DML操作一種數(shù)據(jù)庫(kù)對(duì)象通過(guò)指針加速 Oracle 服務(wù)器的查詢(xún)速度通過(guò)快速定位數(shù)據(jù)的方法，減少磁盤(pán) I/O索引與表相互獨(dú)立Oracle 服務(wù)器自動(dòng)使用和維護(hù)索引創(chuàng)建索引、自動(dòng)創(chuàng)建: 在定義 PRIMARY KEY 或 UNIQUE 約束后系統(tǒng)自動(dòng)在相應(yīng)的列上創(chuàng)建唯一性索引、手動(dòng)創(chuàng)建: 用戶(hù)可以在其它列上創(chuàng)建非唯一的索引，以加速查詢(xún)索引分類(lèi)B-tree 索引 默認(rèn)，適用于基數(shù)比較高，冗余數(shù)量較少的時(shí)候位圖索引，適用于基數(shù)比較第，冗余數(shù)量較高的時(shí)候適合創(chuàng)建B

19、-TREE索引的時(shí)機(jī)：1.列值基數(shù)比較高（重復(fù)率比較低）2.不以空值做條件3.列經(jīng)常出現(xiàn)在條件子句中4.表很大5.經(jīng)常做查詢(xún)操作，而不是DML操作6.滿(mǎn)足條件的數(shù)據(jù)不超過(guò)總記錄數(shù)的15%7.模糊查詢(xún)不走索引，如果模糊查詢(xún)想使用索引，必須用全文檢索技術(shù)創(chuàng)建索引在一個(gè)或多個(gè)列上創(chuàng)建索引CREATE INDEX indexON table (column, column.);在表 EMPLOYEES的列 LAST_NAME 上創(chuàng)建索引CREATE INDEX emp_last_name_idxON employees(last_name);Index created.創(chuàng)建唯一索引alter tabl

20、e new_emp add constraints newemp_empid_pk primary key(employee_id)select * from new_emp創(chuàng)建普通索引create index emp_lastname_idx on new_EMP(last_name)查詢(xún)索引可以使用數(shù)據(jù)字典視圖USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息SELECTic.index_name, ic.column_name,ic.column_position col_pos,ix.uniquenessFROMuser_indexes ix, user_i

21、nd_columns icWHEREic.index_name = ix.index_nameANDic.table_name = 'EMPLOYEES'select * from new_emp where employee_id= 174select * from new_emp where last_name ='King'/King=king創(chuàng)建基于函數(shù)的索引create index newemp_lastname_idx on new_emp(LOWER(last_name)select * from new_emp where LOWER(last_

22、name)='King'基于函數(shù)的索引基于函數(shù)的索引是一個(gè)基于表達(dá)式的索引索引表達(dá)式由列, 常量, SQL 函數(shù)和用戶(hù)自定義的函數(shù)CREATE INDEX upper_dept_name_idxON departments(UPPER(department_name);Index created.SELECT *FROM departmentsWHERE UPPER(department_name) = 'SALES'位圖索引位圖索引是在基數(shù)較小，即重復(fù)率很大的列值中創(chuàng)建比較適用 create bitmap index emp_jobid_bmidxon empcopy (job_id);create index newemp_jobid_idx on new_emp(job_id)select * from new_emp where job_id='IT_PROG'RBO和CBORBO ： 基于規(guī)則的優(yōu)化方式(Rule-Based Optimization ）CBO : 基于代價(jià)的優(yōu)化方式(Cost-Based Optimization )刪除索引使用DROP INDEX 命令刪除索引DROP INDEX ind