Oracle資料庫的SQL語句之完整性約束——基礎篇

来源:https://www.cnblogs.com/hellohowlow/archive/2018/01/10/8261654.html
-Advertisement-
Play Games

SELECT * FROM tb_clazz;SELECT * FROM tb_student; INSERT INTO tb_clazz(code,NAME,bzr) VALUES('1401','14電腦1班','廖老師');INSERT INTO tb_clazz(code,NAME,bzr ...


SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

INSERT INTO tb_clazz(code,NAME,bzr) VALUES('1401','14電腦1班','廖老師');
INSERT INTO tb_clazz(code,NAME,bzr) VALUES('1402','14電腦2班','謝老師');

INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(1,'周傑倫','男',31,'123','[email protected]','臺灣');
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(2,'林志玲','女',41,'123','[email protected]','臺灣');
-- 以下數據喪失數據完整性
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(3,null,'妖',200,'123','[email protected]','臺灣');

/**
大部分資料庫支持下麵幾類完整性約束:
CHECK 檢查約束
-- NOT NULL 非空約束（特殊的檢查約束）
UNIQUE KEY 唯一約束
PRIMARY KEY 主鍵約束
FOREIGN KEY 外鍵約束
*/

-- 建表時，直接在列後面創建約束，稱為列級約束，資料庫會預設分配唯一的約束名SYS_624
CREATE TABLE tb_student(
ID INT,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = '男' OR sex = '女'),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100)
);

SELECT * FROM tb_student;

INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(1,'周傑倫','男',31,'123','[email protected]','臺灣');

-- 查詢兩條相同數據
INSERT INTO tb_student(ID,name,sex,age,phone,email,address)
VALUES(1,'林志玲','女',41,'111','[email protected]','臺灣');

INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(1,'林志玲','女',41,'臺灣');

-- 刪除其中一條數據,發現無法唯一確定某一條記錄
DELETE FROM tb_student WHERE


/*******************主鍵**************************/
/**
重點
格式 PRIMARY KEY
作用：用來唯一確定一行記錄
（1）主鍵是表中唯一確定一行數據的欄位，主鍵從功能上看相當於非空且唯一
（2）一個表中只允許一個主鍵
（3）主鍵欄位可以是單欄位或者是多欄位的組合
（4）當建立主鍵約束時，Oracle為主鍵創建對應的索引
（5）現代資料庫建模，建議一張表一定要有主鍵，並且主鍵應該和業務數據無關，建議使用自動增長的自然數。
*/
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = '男' OR sex = '女'),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100)
);

-- 因為主鍵非空並且唯一，所以插入時必須插入，並且不能相同
INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(1,'林志玲','女',41,'臺灣');
INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(2,'林志玲','女',41,'臺灣');
-- 刪除其中一條數據,根據主鍵id刪除
DELETE FROM tb_student WHERE ID = 2;


/*****************外鍵****************************/
-- 保存學生數據時需要知道學生的班級情況
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = '男' OR sex = '女'),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazzcode VARCHAR(18),
clazzname VARCHAR(18),
clazzbzr VARCHAR(18)
);
SELECT * FROM tb_student;

INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address,clazzcode,clazzname,clazzbzr)
VALUES(1,'周傑倫','男',31,'123','[email protected]','臺灣','1401','14電腦1班','廖老師');

INSERT INTO tb_student(ID,name,sex,age,phone,email,address,clazzcode,clazzname,clazzbzr)
VALUES(2,'林志玲','女',41,'111','[email protected]','臺灣','1402','14電腦2班','廖老師');
/*
以上資料庫插入數據問題：如果有100人在1401班，則100人的clazzcode,clazzname,clazzbzr信息
完全一致，一個學生的clazzcode,clazzname,clazzbzr信息存儲需要17個位元組，100個學生需要17*100個位元組，
而這些信息基本屬於重覆的（數據冗餘）,浪費存儲空間。

解決數據冗餘問題使用外鍵。
關係資料庫就是指兩個表通過外鍵產生關聯關係。
例如：通過tb_student表的clazz_id列讓tb_clazz表和tb_student表產生了關聯的關係。
*/
CREATE TABLE tb_clazz(
ID INT PRIMARY KEY,
CODE VARCHAR(18),
NAME VARCHAR(18),
bzr VARCHAR(18)
);
INSERT INTO tb_clazz(id,code,NAME,bzr) VALUES(1,'1401','14電腦1班','廖老師');
INSERT INTO tb_clazz(id,code,NAME,bzr) VALUES(2,'1402','14電腦2班,'謝老師');

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = '男' OR sex = '女'),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazz_id INT REFERENCES tb_clazz(ID)
);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address,clazz_id)
VALUES(1,'周傑倫','男',31,'123','[email protected]','臺灣',1);

INSERT INTO tb_student(ID,name,sex,age,phone,email,address,clazz_id)
VALUES(2,'林志玲','女',41,'111','[email protected]','臺灣',1);

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;


/**
格式 FOREIGN KEY (外鍵列名) REFERENCES 主表(參照列)
作用：外鍵是構建於一個表的兩個欄位或者兩個表的兩個欄位之間的關係，解決數據冗餘（多餘）問題。
例如：通過clazz_id欄位將tb_claz表和tb_student表關聯起來。

clazz_id INT REFERENCES tb_clazz(ID)

外鍵要註意的問題：
1.子(從)表[tb_student]外鍵列[clazz_id]的值必須在父(主)表[tb_clazz]參照列[id]值的範圍內，
或者為空（也可以加非空約束，強制不允許為空）。

2.外鍵[clazz_id]參照的只能是主表[tb_clazz]主鍵或者唯一鍵，保證子表記錄可以準確定位到被參照的記錄。
3.當主表[tb_clazz]的記錄被子表[tb_student]參照時，主表記錄不允許被刪除。
4.建表時可以增加以下設置：
（1）ON DELETE CASCADE:當父表中的行被刪除的時候，同時刪除在子表中依靠的行

clazz_id INT REFERENCES tb_clazz(ID) ON DELETE CASCADE

（2）ON DELETE SET NULL:將依靠的外鍵值轉換為空值

clazz_id INT REFERENCES tb_clazz(ID) ON SET NULL
*/

-- 1.子(從)表[tb_student]外鍵列[clazz_id]的值必須在父(主)表[tb_clazz]參照列[id]值的範圍內，
-- 或者為空（也可以加非空約束，強制不允許為空）。
INSERT INTO tb_student(ID,name,sex,age,phone,email,address,clazz_id)
VALUES(3,'柳岩','女',36,'222','[email protected]','臺灣',9);

-- 2.外鍵[clazz_id]參照的只能是主表[tb_clazz]主鍵或者唯一鍵，保證子表記錄可以準確定位到被參照的記錄。
-- 反證法證明，tb_clazz參照的不是主鍵也不是唯一鍵,會出現的問題
CREATE TABLE tb_clazz(
ID INT ,
CODE VARCHAR(18),
NAME VARCHAR(18),
bzr VARCHAR(18)
);
INSERT INTO tb_clazz(id,code,NAME,bzr) VALUES(1,'1401','14電腦1班','廖老師');
INSERT INTO tb_clazz(id,code,NAME,bzr) VALUES(1,'1402','14電腦2班','謝老師');

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = '男' OR sex = '女'),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazz_id INT
);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address,clazz_id)
VALUES(1,'周傑倫','男',31,'123','[email protected]','臺灣',1);


SELECT * FROM tb_clazz;
SELECT * FROM tb_student;
-- 3.當主表[tb_clazz]的記錄被子表[tb_student]參照時，主表記錄不允許被刪除。
DELETE FROM tb_clazz WHERE ID = 1;
-- 解決方案1：先刪除子表數據，再刪除父表數據
DELETE FROM tb_student WHERE clazz_id = 1;
-- 解決方案2：先將子表關聯數據設置為null，再刪除父表數據
UPDATE tb_student SET clazz_id = NULL WHERE clazz_id = 1;
-- 解決方案3：先將子表數據修改，再刪除父表數據
UPDATE tb_student SET clazz_id = 2 WHERE clazz_id = 1;


-- 4. ON DELETE CASCADE指當刪除父表數據時，級聯刪除子表數據，對應上面的方案1
-- ON SET NULL 指當刪除父表數據時，將關聯的數據設置為null，對應上面的方案2

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 使用.Net+非關係型資料庫MongoDB 實現LBS商家按距離排序_按離我最近排序
    .Net MongoDB LBS地理位置定位 開發過程，實現商家按距離排序 前言： 在使用美團點外賣，看電影，找好吃的時候，經常會註意到軟體有一個按距離排序，找離我最近的商家，心中有一些疑問，.Net+Sql Server是如何做到這一需求的？發現使用關係型資料庫Sql Server實現這一需求是有 ...
  • 【筆記】.NET開發環境下使用PostgreSQL+Oracle_fdw 實現兩個資料庫之間數據交互操作（一）
    一 前言 以往公司開發的地理信息系統數據都是存儲在oracle中，然而在地圖數據的分析和計算中一直存在不小的問題，所以今次我們嘗試使用PostgreSQL+PostGIS來解決這一問題。 本篇筆記的目的就是記錄在研究這一開發路徑期間的“學習的內容”、“遇到的問題和解決過程”以及“簡單的實踐內容”以備 ...
  • SQL中的SELECT 簡單查詢語句總結
    --scott用戶不能使用，使用system登錄--修改用戶scott賬戶解鎖ALTER USER SCOTT ACCOUNT UNLOCK;--重新設置密碼identified被識別的alter user scott identified by tiger; --選擇 所有欄位 scott用戶部門 ...
  • Kettle使用心得彙總系列一
    Kettle使用心得彙總系列一 一、kettle實現的功能： kettle可以實現從不同數據源（excel、資料庫、文本文件等）獲取數據，然後將數據進行整合、轉換處理，可以再將數據輸出到指定的位置（excel、資料庫、文本文件）等； 通過kettle處理大量數據非常方便，如果window伺服器或者L ...
  • oracle 中的閃回
    概述： 閃回技術是Oracle強大資料庫備份恢復機制的一部分，在資料庫發生邏輯錯誤的時候，閃回技術能提供快速且最小損失的恢復（多數閃回功能都能在資料庫聯機狀態下完成）。需要註意的是，閃回技術旨在快速恢復邏輯錯誤，對於物理損壞或是介質丟失的錯誤，閃回技術就回天乏術了，還是得藉助於Oracle一些高級的 ...
  • Mysql-5.7.20主從複製測試[20180110]
    前言 Mysql 5.7.20測試主從複製 環境 主庫 192.168.1.59 t-xi-sonar01 從庫 192.168.1.51 t-xi-orc01 設定主機host文件 主庫 [root@t-xi-sonar01 ~]# cat /etc/hosts 127.0.0.1 localho ...
  • SqlServer知識點
    在公司天天寫Sql寫,存儲過程,但是公司工具模板把創建的語句都寫好了,只負責寫裡面的邏輯,久而久之,創建語句都不會寫了。還有一些知識點都很模糊,平常使用的時候都不清楚,稀里糊塗的就在用。在這裡整理一下。鞏固複習。 一.存儲過程。 1.存儲過程類似編程語言的裡面的函數,方法。將Sql語句封裝在裡面,方 ...
  • mysql主主複製彙總整理
    mysql主主複製彙總整理 一、Mysql主主、主從複製主要思路： 1、mysql複製實質： 就是其他的MySQL資料庫伺服器將這個數據變更的二進位日誌在本機上再執行一遍，因此非常重要的一點是mysql資料庫中必須要開啟二進位日誌； 2、Mysql主從複製： 就是A為主資料庫，B為從資料庫，B將A中 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類