添加約束語句格式：\x0d\x0aalter table 表名 add constraint 主鍵約束名 primary key(主鍵列名表序列);\x0d\x0a如：\x0d\x0aalter table 修課表 add constraint pk_xh_kc primary key(學號,課程號);\x0d\x0a\x0d\x0a刪除約束語句格式：\x0d\x0aalter table 表名 drop CONSTRAINT 主鍵約束名;\x0d\x0a如：\x0d\x0aalter table 修課表 drop CONSTRAINT pk_xh_kc;
創(chuàng)新互聯(lián) - 成都服務器托管，四川服務器租用，成都服務器租用，四川網(wǎng)通托管,綿陽服務器托管，德陽服務器托管，遂寧服務器托管，綿陽服務器托管，四川云主機，成都云主機，西南云主機，成都服務器托管，西南服務器托管，四川/成都大帶寬，大帶寬服務器，四川老牌ＩＤＣ服務商
表創(chuàng)建標準語法:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] , …);
--設計要求：建立一張用來存儲學生信息的表，表中的字段包含了學生的學號、姓名、年齡、入學日期、年級、班級、email等信息，
--并且為grade指定了默認值為1，如果在插入數(shù)據(jù)時不指定grade得值，就代表是一年級的學生
--DML是不需要commit的，隱式事務
create table student
(
stu_id number(10),
name varchar2(20),
age number(2),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50)
);
-- 注意日期格式要轉換，不能是字符串，varchar2類型要用引號，否則出現(xiàn)類型匹配
--DML 需要收到commit
insert into student values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'2','1',' 123@qq.com ');
insert into student(stu_id,name,age,hiredate,classes,email) values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'1',' 1234@qq.com ');
select * from student;
-- 給表添加列，添加新列時不允許為not null,因為與舊值不兼容
alter table student add address varchar(100);
-- 刪除列
alter table student drop column address;
--修改列
alter table student modify(email varchar2(100));
正規(guī)表設計使用power disinger
--表的重命名
rename student to stu;
-- 表刪除
drop table stu;
**
在刪除表的時候，經(jīng)常會遇到多個表關聯(lián)的情況(外鍵)，多個表關聯(lián)的時候不能隨意刪除，使用如下三種方式:
2.表的約束（constraint）
約束:創(chuàng)建表時，指定的插入數(shù)據(jù)的一些規(guī)則
約束是在表上強制執(zhí)行的數(shù)據(jù)校驗規(guī)則
Oracle 支持下面五類完整性約束:
1). NOT NULL 非空約束 ---- 插入數(shù)據(jù)時列值不能空
2). UNIQUE Key 唯一鍵約束 ----限定列唯一標識，唯一鍵的列一般被用作索引
3). PRIMARY KEY 主鍵約束 ----唯一且非空，一張表最好有主鍵，唯一標識一行記錄
4). FOREIGN KEY 外鍵約束---多個表間的關聯(lián)關系，一個表中的列值，依賴另一張表某主鍵或者唯一鍵
-- 插入部門編號為50的，部門表并沒有編號為50的，報錯
insert into emp(empno,ename,deptno) values(9999,'hehe',50);
5). CHECK 自定義檢查約束---根據(jù)用戶需求去限定某些列的值，使用check約束
-- 添加主鍵約束/not null約束/check約束/唯一鍵約束
create table student
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age0 and age126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
);
-- 添加外鍵約束
create table stu
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age0 and age126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
FOREIGN KEY(deptno) references dept(deptno)
);
-- 創(chuàng)建表時沒添加外鍵約束 也可以修改 其中fk_0001為外鍵名稱
alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno);
索引創(chuàng)建有兩種方式:
組合索引：多個列組成的索引
--索引：加快數(shù)據(jù)剪碎
create index i_ename on emp(ename);
--當創(chuàng)建某個字段索引后，查詢某個字段會自動使用到索引
select * from emp where ename = 'SMITH';
--刪除索引 索引名稱也是唯一的
drop index i_ename;
一些概念：
回表：
覆蓋索引
組合索引
最左匹配
;?? 外鍵約束保證參照完整性 外鍵約束限定了一個列的取值范圍 一個例子就是限定州名縮寫在一個有限值集合中 這個值集合是另外一個控制結構——一張父表
下面我們創(chuàng)建一張參照表 它提供了完整的州縮寫列表 然后使用參照完整性確保學生們有正確的州縮寫 第一張表是州參照表 State作為主鍵
CREATE TABLE state_lookup(state????? VARCHAR ( ) ? state_desc VARCHAR ( )) TABLESPACE student_data; ALTER TABLE state_lookup? ADD CONSTRAINT pk_state_lookup PRIMARY KEY (state)? USING INDEX TABLESPACE student_index;
然后插入幾行記錄
INSERT INTO state_lookup VALUES ( CA California );INSERT INTO state_lookup VALUES ( NY New York );INSERT INTO state_lookup VALUES ( NC North Carolina );
我們通過實現(xiàn)父子關系來保證參照完整性 圖示如下
外鍵字段存在于Students表中|State_lookup? |? 是State字段 ?? 一個外鍵必須參照主鍵或Unique字段?|?????????? 這個例子中 我們參照的是State字段?|?????????? 它是一個主鍵字段（參看DDL）?????????? /|\? |? Students??? |?
上圖顯示了State_Lookup表和Students表間一對多的關系 State_Lookup表定義了州縮寫通用集合——在表中每一個州出現(xiàn)一次 因此 State_Lookup表的主鍵是State字段
State_Lookup表中的一個州名可以在Students表中出現(xiàn)多次 有許多學生來自同一個州 一次 在表State_Lookup和Students之間參照完整性實現(xiàn)了一對多的關系
外鍵同時保證Students表中State字段的完整性 每一個學生總是有個State_lookup表中成員的州縮寫
外鍵約束創(chuàng)建在子表 下面在students表上創(chuàng)建一個外鍵約束 State字段參照state_lookup表的主鍵
創(chuàng)建表
CREATE TABLE students(student_id??? VARCHAR ( ) NOT NULL student_name? VARCHAR ( ) NOT NULL college_major VARCHAR ( ) NOT NULL status??????? VARCHAR ( ) NOT NULL state???????? VARCHAR ( ) license_no??? VARCHAR ( )) TABLESPACE student_data;
創(chuàng)建主鍵
ALTER TABLE studentsADD CONSTRAINT pk_students PRIMARY KEY (student_id)USING INDEX TABLESPACE student_index;
創(chuàng)建Unique約束
ALTER TABLE studentsADD CONSTRAINT uk_students_licenseUNIQUE (state license_no)USING INDEX TABLESPACE student_index;
創(chuàng)建Check約束
ALTER TABLE studentsADD CONSTRAINT ck_students_st_licCHECK ((state IS NULL AND license_no IS NULL) OR(state IS NOT NULL AND license_no is NOT NULL));
創(chuàng)建外鍵約束
ALTER TABLE studentsADD CONSTRAINT fk_students_stateFOREIGN KEY (state) REFERENCES state_lookup (state);
一 Errors的四種類型
參照完整性規(guī)則在父表更新刪除期間和子表插入更新期間強制執(zhí)行 被參照完整性影響的SQL語句是 PARENT UPDATE?? 父表更新操作? 不能把State_lookup表中的state值更新為students表仍在使用而State_lookup表中卻沒有的值
PARENT DELETE?? 父表刪除操作? 不能刪除State_lookup表中的state值后導致students表仍在使用而state_lookup表中卻沒有這個值
CHILD INSERT??? 子表插入操作? 不能插入一個state_llokup表中沒有的state的值CHILD UPDATE??? 子表更新操作? 不能把state的值更新為state_lookup表中沒有的state的值
下面示例說明四種錯誤類型
測試表結構及測試數(shù)據(jù)如下
STATE_LOOKUP State????????? State DescriptionCA???????????? CaliforniaNY???????????? New YorkNC???????????? North Carolina STUDENTS Student ID??? Student Name??? College Major??? Status??? State????? License NOA ????????? John??????????? Biology????????? Degree???? NULL????? NULLA ????????? Mary??????????? Math/Science???? Degree???? NULL????? NULLA ????????? Kathryn???????? History????????? Degree???? CA??????? MV A ????????? Steven????????? Biology????????? Degree???? NY??????? MV A ????????? William???????? English????????? Degree???? NC??????? MV
） PARENT UPDATE
SQL UPDATE state_lookup? ? SET state = XX ? ? WHERE state = CA ; UPDATE state_lookup*ERROR at line :ORA : integrity constraint (SCOTT FK_STUDENTS_STATE)violated 每 child record found
） PARENT DELETE
SQL DELETE FROM state_lookup???? ? WHERE state = CA ; DELETE FROM state_lookup*ERROR at line :ORA : integrity constraint (SCOTT FK_STUDENTS_STATE)violated 每 child record found
） CHILD INSERT
SQL INSERT INTO STUDENTS? ? VALUES ( A ? ? Joseph History Degree XX MV ); INSERT INTO STUDENTS*ERROR at line :ORA : integrity constraint (SCOTT FK_STUDENTS_STATE)violated parent key not found
） CHILD UPDATE
SQL UPDATE students? ? SET state = XX ? ? WHERE student_id = A ; UPDATE students*ERROR at line :ORA : integrity constraint (SCOTT FK_STUDENTS_STATE)violated parent key not found
上面四種類型錯誤都有一個同樣的錯誤代碼 ORA
參照完整性是數(shù)據(jù)庫設計的關鍵一部分 一個既不是其他表的父表也不是子表的表是非常少的
二 級聯(lián)刪除
外鍵語法有個選項可以指定級聯(lián)刪除特征 這個特征僅作用于父表的刪除語句
使用這個選項 父表的一個刪除操作將會自動刪除所有相關的子表記錄
使用創(chuàng)建外鍵約束的DELETE CASCADE選項 然后跟著一條delete語句 刪除state_lookup表中California的記錄及students表中所有有California執(zhí)照的學生
ALTER TABLE studentsADD CONSTRAINT fk_students_stateFOREIGN KEY (state) REFERENCES state_lookup (state)ON DELETE CASCADE;執(zhí)行刪除語句 DELETE FROM state_lookup WHERE state = CA ;
然后再查詢students表中的數(shù)據(jù) 就沒有了字段state值為CA的記錄了
如果表間有外鍵關聯(lián) 但沒有使用級聯(lián)刪除選項 那么刪除操作將會失敗
定義一個級聯(lián)刪除時需要考慮下面問題
級聯(lián)刪除是否適合本應用？從一個父參照表的以外刪除不應該刪除客戶帳號
定義的鏈是什么？查看表與其他表的關聯(lián) 考慮潛在的影響和一次刪除的數(shù)量級及它會帶來什么樣的影響
如果不能級聯(lián)刪除 可設置子表外鍵字段值為null 使用on delete set null語句（外鍵字段不能設置not null約束）
ALTER TABLE studentsADD CONSTRAINT fk_students_stateFOREIGN KEY (state) REFERENCES state_lookup (state)ON DELETE SET NULL;
三 參照字段語法結構
創(chuàng)建外鍵約束是 外鍵字段參照父表的主鍵或Unique約束字段 這種情況下可以不指定外鍵參照字段名 如下 ALTER TABLE students ADD CONSTRAINT fk_students_state FOREIGN KEY （state） REFERENCES state_lookup 當沒有指定參照字段時 默認參照字段是父表的主鍵
如果外鍵字段參照的是Unique而非Primary Key字段 必須在add constraint語句中指定字段名
四 不同用戶模式和數(shù)據(jù)庫實例間的參照完整性
lishixinzhi/Article/program/Oracle/201311/17654
工具/材料
SQL Developer
01
首先打開SQL Developer軟件，找一個沒有主鍵約束的表，如下圖所示
02
然后我們新建一個查詢，在界面中輸入如下的約束修改語句，如下圖所示，主要通過add constranint來添加約束
03
編寫完約束添加語句以后，點擊工具欄中的執(zhí)行按鈕，如下圖所示，如果輸出欄顯示已變更則證明主鍵約束創(chuàng)建成果
04
然后我們進入STUDENT表的約束添加頁中可以看到，我們加的主鍵約束已經(jīng)添加進去了，如下圖所示
05
另外，創(chuàng)建表的時候可以直接添加主鍵約束，如下圖所示，直接在表創(chuàng)建語句中添加constraint即可
06
表創(chuàng)建完以后，記得在左側刷新數(shù)據(jù)庫信息，如下圖所示，因為你創(chuàng)建的信息不刷新的話不會更新
07
最后打開你所創(chuàng)建的表，看到你添加的主鍵約束已經(jīng)加入進來了，這種方式的效果alter的方式是一樣的
重慶分公司
重慶分公司
