如何建立和約束資料庫中的表 - AnalyticDB

AnalyticDB PostgreSQL版資料庫中的表與其它關係型資料庫中的表類似，不同的是表中的行被分布在不同Segment上，表的分布策略決定了在不同Segment上面的分布情況。

建立普通表

CREATE TABLE命令用於建立一個表，建立表時可以定義以下內容：

使用CREATE TABLE命令建立表，格式如下：

CREATE TABLE table_name ( 
[ { column_name data_type [ DEFAULT default_expr ]   -- 表的列定義
   [column_constraint [ ... ]                        -- 列的約束定義
] 
   | table_constraint                                -- 表層級的約束定義                            
   ])
   [ WITH ( storage_parameter=value [, ... ] )       -- 表格儲存體格式定義
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]  -- 表的分布鍵定義          
   [ partition clause]                               -- 表的分區定義

樣本：

以下樣本中的建表語句建立了一個表，使用trans_id作為分布鍵，並基於date設定了RANGE資料分割函數。

CREATE TABLE sales (
  trans_id int,
  date date, 
  amount decimal(9,2), 
  region text)
  DISTRIBUTED BY (trans_id)  
  PARTITION BY RANGE(date)    
  (start (date '2018-01-01') inclusive
   end (date '2019-01-01') exclusive every (interval '1 month'),
   default partition outlying_dates);

建立暫存資料表

暫存資料表（Temporary Table）會在會話結束時自動刪除，或選擇性地在當前事務結束的時候刪除，用於儲存臨時中間結果。建立暫存資料表的命令如下：

CREATE TEMPORARY TABLE table_name(…)
    [ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}]

說明

暫存資料表的行為在事務塊結束時的行為可以通過上述語句中的ON COMMIT來控制。

PRESERVE ROWS：在事務結束時候保留資料，這是預設的行為。
DELETE ROWS：在每個事務塊結束時，暫存資料表的所有行都將被刪除。
DROP：在當前事務結束時，會刪除暫存資料表。

樣本：

建立一個暫存資料表，事務結束時候刪除該暫存資料表。

CREATE TEMPORARY TABLE temp_foo (a int, b text) ON COMMIT DROP;

資料表條件約束的定義

您可以在列和表上定義約束來限制表中的資料，但是有以下一些限制：

CHECK約束引用的列只能在其所在的表中。
UNIQUE和PRIMARY KEY約束必須包含分布鍵列，UNIQUE和PRIMARY KEY約束不支援追加最佳化表和列存表。
允許FOREIGN KEY 約束，但實際上並不會做外鍵約束檢查。
分區表上的約束必須應用到所有的分區表上，不能只應用於部分分區表。

約束命令格式如下：

UNIQUE ( column_name [, ... ] )
   | PRIMARY KEY ( column_name [, ... ] ) 
   | CHECK ( expression )
   | FOREIGN KEY ( column_name [, ... ] )
            REFERENCES table_name [ ( column_name [, ... ] ) ]
            [ key_match_type ]
            [ key_action ]
            [ key_checking_mode ]

檢查約束（Check Constraints）

檢查約束（Check Constraints）指定列中的值必須滿足一個布林運算式，例如：

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

非空約束（Not-Null Constraints）

非空約束（Not-Null Constraints）指定列不能有空值，例如：

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

唯一約束（Unique Constraints）

唯一約束（Unique Constraints）確保一列或者一組列中包含的資料對於表中所有的行都是唯一的。包含唯一約束的表必須是雜湊分布，並且約束列需要包含分布鍵列，例如：

CREATE TABLE products
       ( product_no integer UNIQUE,
         name text,
         price numeric)
      DISTRIBUTED BY (product_no);

主鍵約束（Primary Keys Constraints）

主鍵約束（Primary Keys Constraints）是一個UNIQUE約束和一個 NOT NULL約束的組合。包含主鍵約束的表必須是雜湊分布，並且約束列需要包含分布鍵列。如果一個表具有主鍵，這個列（或者這一組列）會被預設選中為該表的分布鍵，例如：

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

AnalyticDB：資料表管理