PosegreSQL基礎回顧(第 5 章數據定義)

來源：http://www.postgres.cn/docs/11/ 5.1. 表基礎 SQL並不保證表中行的順序。當一個表被讀取時，表中的行將以非特定順序出現，除非明確地指定需要排序。嘗試移除一個不存在的表會引起錯誤。然而，在SQL腳本中在創建每個表之前無條件地嘗試移除它的做法是很常見的，即使發 ...

來源：http://www.postgres.cn/docs/11/

5.1. 表基礎

SQL並不保證表中行的順序。當一個表被讀取時，表中的行將以非特定順序出現，除非明確地指定需要排序。

嘗試移除一個不存在的表會引起錯誤。然而，在SQL腳本中在創建每個表之前無條件地嘗試移除它的做法是很常見的，即使發生錯誤也會忽略之，因此這樣的腳本可以在表存在和不存在時都工作得很好（如果你喜歡，可以使用DROP TABLE IF EXISTS變體來防止出現錯誤消息，但這並非標準SQL）。

5.3.1. 檢查約束

一個檢查約束是最普通的約束類型。它允許我們指定一個特定列中的值必須要滿足一個布爾表達式。例如，為了要求正值的產品價格，我們可以使用：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

5.3.2. 非空約束

一個非空約束總是被寫成一個列約束。一個非空約束等價於創建一個檢查約束CHECK (column_name IS NOT NULL)，但在PostgreSQL中創建一個顯式的非空約束更高效。這種方式創建的非空約束的缺點是我們無法為它給予一個顯式的名稱。

NOT NULL約束有一個相反的情況：NULL約束。這並不意味著該列必須為空，進而肯定是無用的。相反，它僅僅選擇了列可能為空的預設行為。SQL標準中並不存在NULL約束，因此它不能被用於可移植的應用中（PostgreSQL中加入它是為了和某些其他資料庫系統相容）。但是某些用戶喜歡它，因為它使得在一個腳本文件中可以很容易的進行約束切換。例如，初始時我們可以：

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

然後可以在需要的地方插入NOT關鍵詞。

5.3.3. 唯一約束

唯一約束保證\在一列中或者一組列中保存的數據在表中所有行間是唯一的。

通常，如果表中有超過一行在約束所包括列上的值相同，將會違反唯一約束。但是在這種比較中，兩個空值被認為是不同的。

這意味著即便存在一個唯一約束，也可以存儲多個在至少一個被約束列中包含空值的行。

這種行為符合SQL標準，但我們聽說一些其他SQL資料庫可能不遵循這個規則。所以在開發需要可移植的應用時應註意這一點。

5.3.4. 主鍵

一個主鍵約束表示可以用作表中行的唯一標識符的一個列或者一組列。這要求那些值都是唯一的並且非空。

一個表最多只能有一個主鍵（可以有任意數量的唯一和非空約束，它們可以達到和主鍵幾乎一樣的功能，但只能有一個被標識為主鍵）。

關係資料庫理論要求每一個表都要有一個主鍵。但PostgreSQL中並未強制要求這一點，但是最好能夠遵循它。

5.3.5. 外鍵

一個外鍵約束指定一列（或一組列）中的值必須匹配出現在另一個表中某些行的值。我們說這維持了兩個關聯表之間的引用完整性。

我們知道外鍵不允許創建與任何產品都不相關的訂單。但如果一個產品在一個引用它的訂單創建之後被移除會發生什麼？SQL允許我們處理這種情況。直觀上，我們有幾種選項：

不允許刪除一個被引用的產品
同時也刪除引用產品的訂單
其他？

為了說明這些，讓我們在上面的多對多關係例子中實現下麵的策略：當某人希望移除一個仍然被一個訂單引用（通過order_items）的產品時，我們組織它。如果某人移除一個訂單，訂單項也同時被移除：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

限制刪除或者級聯刪除是兩種最常見的選項。

RESTRICT阻止刪除一個被引用的行。

NO ACTION表示在約束被檢察時如果有任何引用行存在，則會拋出一個錯誤，這是我們沒有指定任何東西時的預設行為（這兩種選擇的本質不同在於NO ACTION允許檢查被推遲到事務的最後，而RESTRICT則不會）。

CASCADE指定當一個被引用行被刪除後，引用它的行也應該被自動刪除。

還有其他兩種選項：SET NULL和SET DEFAULT。這些將導致在被引用行被刪除後，引用行中的引用列被置為空值或它們的預設值。註意這些並不會是我們免於遵守任何約束。例如，如果一個動作指定了SET DEFAULT，但是預設值不滿足外鍵約束，操作將會失敗。

與ON DELETE相似，同樣有ON UPDATE可以用在一個被引用列被修改（更新）的情況，可選的動作相同。在這種情況下，CASCADE意味著被引用列的更新值應該被覆制到引用行中。

正常情況下，如果一個引用行的任意一個引用列都為空，則它不需要滿足外鍵約束。如果在外鍵定義中加入了MATCH FULL，一個引用行只有在它的所有引用列為空時才不需要滿足外鍵約束（因此空和非空值的混合肯定會導致MATCH FULL約束失敗）。如果不希望引用行能夠避開外鍵約束，將引用行聲明為NOT NULL。

一個外鍵所引用的列必須是一個主鍵或者被唯一約束所限制。這意味著被引用列總是擁有一個索引（位於主鍵或唯一約束之下的索引），因此在其上進行的一個引用行是否匹配的檢查將會很高效。由於從被引用表中DELETE一行或者UPDATE一個被引用列將要求對引用表進行掃描以得到匹配舊值的行，在引用列上建立合適的索引也會大有益處。由於這種做法並不是必須的，而且創建索引也有很多種選擇，所以外鍵約束的定義並不會自動在引用列上創建索引。

更多關於更新和刪除數據的信息請見第 6 章。外鍵約束的語法描述請參考CREATE TABLE。

5.3.6. 排他約束

排他約束保證如果將任何兩行的指定列或表達式使用指定操作符進行比較，至少其中一個操作符比較將會返回否或空值。語法是：

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

詳見CREATE TABLE ... CONSTRAINT ... EXCLUDE。

增加一個排他約束將在約束聲明所指定的類型上自動創建索引。

5.8. 模式

一個資料庫包含一個或多個命名模式，模式中包含著表。模式還包含其他類型的命名對象，包括數據類型、函數和操作符。相同的對象名稱可以被用於不同的模式中二不會出現衝突，例如schema1和myschema都可以包含名為mytable的表。和資料庫不同，模式並不是被嚴格地隔離：一個用戶可以訪問他們所連接的資料庫中的所有模式內的對象，只要他們有足夠的許可權。

下麵是一些使用方案的原因：

允許多個用戶使用一個資料庫並且不會互相干擾。
將資料庫對象組織成邏輯組以便更容易管理。
第三方應用的對象可以放在獨立的模式中，這樣它們就不會與其他對象的名稱發生衝突。

模式類似於操作系統層的目錄，但是模式不能嵌套。

5.8.2. 公共模式

在前面的小節中，我們創建的表都沒有指定任何模式名稱。預設情況下這些表（以及其他對象）會自動的被放入一個名為“public”的模式中。

任何新資料庫都包含這樣一個模式。因此，下麵的命令是等效的：

CREATE TABLE products ( ... );   以及：   CREATE TABLE public.products ( ... );

5.9. 繼承

PostgreSQL實現了表繼承，這對資料庫設計者來說是一種有用的工具（SQL:1999及其後的版本定義了一種類型繼承特性，但和這裡介紹的繼承有很大的不同）。

5.10. 表分區

劃分指的是將邏輯上的一個大表分成一些小的物理上的片。

5.13. 依賴跟蹤

當我們創建一個涉及到很多具有外鍵約束、視圖、觸發器、函數等的表的複雜資料庫結構時，我們隱式地創建了一張對象之間的依賴關係網。例如，具有一個外鍵約束的表依賴於它所引用的表。

為了保證整個資料庫結構的完整性，PostgreSQL確保我們無法刪除仍然被其他對象依賴的對象。

PostgreSQL中的幾乎所有DROP命令都支持CASCADE。當然，其本質的區別隨著對象的類型而不同。我們也可以用RESTRICT代替CASCADE來獲得預設行為，它將阻止刪除任何被其他對象依賴的對象。

PosegreSQL基礎回顧(第 5 章 數據定義)