程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 完整約束三（學習筆記），約束學習筆記

完整約束三（學習筆記），約束學習筆記

編輯：Oracle教程

完整約束三（學習筆記），約束學習筆記

主外鍵約束FK

FOREIGN KEY(FK)

CONSTRAINT fk_外鍵名 FOREIGN KEY(列名) REFERENCES 表2名(列名)--一般表2的主鍵

先刪除
DROP TABLE MEMBER PURGE;

創建表-成員

CREATE TABLE MEMBER (
    mid NUMBER ,
    NAME VARCHAR2(20) NOT NULL,
    --創建主鍵約束
   CONSTRAINT PK_mid PRIMARY KEY(mid)

);

添加數據

INSERT INTO MEMBER(mid,NAME)VALUES(1,'張無忌');
INSERT INTO MEMBER(mid,NAME)VALUES(2,'張三豐');

創建另一張表建議表

CREATE TABLE advice(
      adid NUMBER,
      content CLOB NOT NULL,
      mid NUMBER,
      CONSTRAINT PK_adid PRIMARY KEY(adid)
);

為avvice添加數據

INSERT INTO advice(adid,content,mid)VALUES(1,'應該提倡內部溝通機制，設置總裁郵箱',1);
INSERT INTO advice(adid,content,mid)VALUES(2,'為了公司內部良性發展，所有部門領導應該重新上崗',1);
INSERT INTO advice(adid,content,mid)VALUES(3,'要多開展員工培訓活動，讓員工更加有歸屬感',1);
INSERT INTO advice(adid,content,mid)VALUES(4,'應該開展多元化業務，更加滿足市場需求',2);
INSERT INTO advice(adid,content,mid)VALUES(5,'大力發展技術部門，為本公司設計自己的ERP系統，適應電子化信息發展要求',2);
COMMIT;
--查詢advice表
SELECT * FROM advice;

查詢出每位成員的完整信息和所提出的意見數量

SELECT m.mid,m.name,COUNT(a.mid)
FROM MEMBER m,advice a
WHERE m.mid=a.mid
GROUP BY m.mid,m.name;
可以看出可以正確統計，advice中的MID應該取值范圍應該由成員表中的mid的內容現在並沒有限制

在 advice中增加一條編號不在member中mid范圍內的數據

INSERT INTO advice(adid,content,mid) VALUES(6,'崗位職責透明化',99);
--查詢advice表
SELECT * FROM advice;
--可以添加成功

指定主-外鍵約束

--先刪除

DROP TABLE MEMBER PURGE;

DROP TABLE advice PURGE;

創建同樣的表

創建表成員
CREATE TABLE MEMBER (
    mid NUMBER ,
    NAME VARCHAR2(20) NOT NULL,
    --創建主鍵約束
   CONSTRAINT PK_mid PRIMARY KEY(mid)

);
--創建另一張表建議表
CREATE TABLE advice(
      adid NUMBER,
      content CLOB NOT NULL,
      mid NUMBER,
      CONSTRAINT PK_adid PRIMARY KEY(adid),
      CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid)
);

--添加數據
INSERT INTO MEMBER(mid,NAME)VALUES(1,'劉楠');
INSERT INTO MEMBER(mid,NAME)VALUES(2,'董方卓');
--查詢
SELECT * FROM MEMBER;
--為avvice添加數據
INSERT INTO advice(adid,content,mid)VALUES(1,'應該提倡內部溝通機制，設置總裁郵箱',1);
INSERT INTO advice(adid,content,mid)VALUES(2,'為了公司內部良性發展，所有部門領導應該重新上崗',1);
INSERT INTO advice(adid,content,mid)VALUES(3,'要多開展員工培訓活動，讓員工更加有歸屬感',1);
INSERT INTO advice(adid,content,mid)VALUES(4,'應該開展多元化業務，更加滿足市場需求',2);
INSERT INTO advice(adid,content,mid)VALUES(5,'大力發展技術部門，為本公司設計自己的ERP系統，適應電子化信息發展要求',2);
COMMIT;
--查詢advice表
SELECT * FROM advice;

此時再在 advice中增加一條編號不在member中mid范圍內的數據

INSERT INTO advice(adid,content,mid) VALUES(6,'崗位職責透明化',99);

錯誤的信息就添加不進去了

刪除父表時要先刪除子表對應的數據

示例:刪除member中編號為1的數據

DELETE FROM MEMBER WHERE mid=1;

會報錯

不能刪除

需要刪除子表advice中mid=1的數據再刪除父表member中mid=1的數據
DELETE FROM advice WHERE mid=1;
DELETE FROM MEMBER WHERE mid=1;

這時可以刪除

也可以在創建表時設置級聯

可以在創建表時設置級聯刪除
--在外鍵設置後面設置ON DELETE CASCADE

--先刪除

DROP TABLE advice PURGE;

DROP TABLE MEMBER PURGE;

創建表成員
CREATE TABLE MEMBER (
    mid NUMBER ,
    NAME VARCHAR2(20) NOT NULL,
    --創建主鍵約束
   CONSTRAINT PK_mid PRIMARY KEY(mid)

);
--創建另一張表建議表
CREATE TABLE advice(
      adid NUMBER,
      content CLOB NOT NULL,
      mid NUMBER,
      CONSTRAINT PK_adid PRIMARY KEY(adid),
      CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid) ON DELETE CASCADE              --主外鍵約束添加級聯刪除
);

這樣刪除父表中的記錄時，可以直接刪除

但查詢子表發現子表中的記錄也被刪除掉了

不想刪除子表中的記錄時可以用

在外鍵後加上ON DELETE SET NULL

--刪除表
DROP TABLE advice;
DROP TABLE MEMBER;
--創建表
CREATE TABLE MEMBER(
     mid NUMBER,
     NAME VARCHAR2(30) NOT NULL,
     CONSTRAINT PK_mid PRIMARY KEY(mid)   
);
CREATE TABLE advice(
   adid NUMBER,
   CONTENT CLOB NOT NULL,
   mid NUMBER,
   CONSTRAINT PK_adid PRIMARY KEY(adid),
   CONSTRAINT FK_mid FOREIGN KEY(mid)  REFERENCES MEMBER(mid) ON DELETE SET NULL                --設置刪除時子表記錄相關的列為NULL
);

這樣刪除父表中的記錄時，可以直接刪除

但查詢子表發現子表中的記錄沒有刪除掉，只是相關的字段為NULL

強制刪除表

DROP FROM MEMBER WHERE mid=1 CASCADE CONSTRAINT;

父表會直接刪除，子表內容仍然存在

查看約束

SELECT constraint_name,constraint_type,table_name FROM user_constraints;
--查看EMP表的約束
SELECT constraint_name,constraint_type,table_name
FROM user_constraints
WHERE table_name='EMP'

查看user_cons_columns數據字典
select * from user_cons_columns;

修改約束

增加約束語法

ALTER TABLE 表名稱

ADD CONSTRAINT 約束名稱 約束類型(約束字段)

刪除表
DROP TABLE advice;
DROP TABLE MEMBER;
--創建表
CREATE TABLE MEMBER(
     mid NUMBER,
     NAME VARCHAR2(30) 
       
);
CREATE TABLE advice(
   adid NUMBER,
   CONTENT CLOB ,
   mid NUMBER
);

--為member表的mid字段增加主鍵約束
ALTER TABLE MEMBER
ADD CONSTRAINT PK_mid PRIMARY KEY(mid);
--為member age字段增加檢查約束
--先增加age字段
ALTER TABLE MEMBER
ADD(age NUMBER);
SELECT * FROM MEMBER;
ALTER TABLE MEMBER
ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200); 
--檢查member表中的字段
SELECT constraint_name,constraint_type,table_name
FROM User_Constraints WHERE table_name='MEMBER';
--為name字段設置非空
ALTER TABLE MEMBER
MODIFY(NAME NOT NULL);

啟用和禁用約束

ALTER TABLE 表名稱 ENABLE |DISABLE CONSTRAINT 約束名稱 [CASCADE]

--cascade關閉存在完整性關系的約束（主要是外鍵）

--禁用advcie中的Pk-adid主鍵約束
ALTER TABLE advice DISABLE CONSTRAINT pk_adid;
--禁用memeber表中的pk_mid主鍵約束
ALTER TABLE MEMBER DISABLE CONSTRAINT pk_mid;
--啟用約束
ALTER TABLE 表名稱 ENABLE CONSTRAINT 約束名稱
--啟用memeber表中的pk_mid主鍵約束
ALTER TABLE MEMBER ENABLE CONSTRAINT pk_mid

--刪除約束
ALTER TABLE 表名稱 DROP CONSTRAINT 約束名[CASCADE]
--刪除advcie中的pk_adid約束-無關外鍵
ALTER TABLE advice DROP CONSTRAINT pk_adid;
--刪除member中的pk_mid-有關外鍵
ALTER TABLE MEMBER DROP CONSTRAINT pk_mid CASCADE;