Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | Github | OSChina 博客 | 雲社區 | 雲棲社區 | Facebook | Linkedin | 知乎專欄 | 視頻教程 | About

第 5 章 數據定義(DDL)

目錄

5.1. 模式
5.2. Types
5.2.1. Enumerated Types
5.2.2. uuid
5.3. 表
5.3.1. 預設時間與時區
5.3.2. 漢字做欄位名
5.4. 序列
5.4.1. 等差列 “1,2,3,4,5,6,7,8,9…”
5.4.2. 等差列 “1,3,5,7,9…”
5.4.3. 等差列 “2,4,6,8,10…”
5.4.4. n1+n2
5.5. 約束
5.5.1. 檢查約束
5.5.2. 非空約束
5.5.3. 唯一約束
5.6. 主鍵/外鍵
5.6.1. 主鍵
5.6.2. 外鍵約束
5.6.3. 案例

5.1. 模式

		
3.10       模式

一些用戶為了使某些模組的表看起來清晰,一般他們採用“模組名_表名”:
Auth_user
Auth_group
Bbs_topic
Bbs_message

PostgreSQL不必這樣命名,可以使用Schema(模式)如:
Auth.user
Auth.group
Bbs.topic
Bbs.message

3.10.1 創建模式

CREATE SCHEMA your_schema;
例:
CREATE SCHEMA btob;
CREATE SCHEMA auction;
3.10.2 刪除模式

DROP SCHEMA your_schema;
刪除模式,並且同時刪除模式下的(表,視圖,觸發器,過程……)
DROP SCHEMA your_schema CASCADE;
例:
DROP SCHEMA btob CASCADE;
DROP SCHEMA btob CASCADE;
3.10.3 模式搜索路徑

查看當前模式SHOW search_path ;
netkiller=> SHOW search_path ;
 search_path
--------------
 $user,public
(1 row)
netkiller=> \dt
            List of relations
 Schema |    Name     | Type  |   Owner
--------+-------------+-------+-----------
 public | company     | table | netkiller
 public | group       | table | netkiller
 public | groupmember | table | netkiller
 public | guestbook   | table | netkiller
 public | prodorder   | table | netkiller
 public | role        | table | netkiller
 public | rolemember  | table | netkiller
 public | system_log  | table | netkiller
 public | templates   | table | netkiller
 public | trust       | table | netkiller
 public | user        | table | netkiller
 public | user_log    | table | netkiller
 public | userinfo    | table | netkiller
(13 rows)
如果不設置模式搜索路徑,“\dt”只顯示public模式下的表。
設置模式SET search_path TO public,btob,auction;
netkiller=> SET search_path TO public,btob,auction;
SET
netkiller=> \dt
              List of relations
 Schema  |     Name      | Type  |   Owner
---------+---------------+-------+-----------
 auction | messages      | table | netkiller
 auction | product       | table | netkiller
 auction | product_order | table | netkiller
 btob    | directory     | table | netkiller
 btob    | trade         | table | netkiller
 btob    | trade_message | table | netkiller
 public  | company       | table | netkiller
 public  | group         | table | netkiller
 public  | groupmember   | table | netkiller
 public  | guestbook     | table | netkiller
 public  | prodorder     | table | netkiller
 public  | role          | table | netkiller
 public  | rolemember    | table | netkiller
 public  | system_log    | table | netkiller
 public  | templates     | table | netkiller
 public  | trust         | table | netkiller
 public  | user          | table | netkiller
 public  | user_log      | table | netkiller
 public  | userinfo      | table | netkiller
(19 rows)

netkiller=>

-- ======================================================================
-- 'btob.directory'
-- ======================================================================
Drop table btob.directory CASCADE;

Create table btob.directory
(
    "id" Serial NOT NULL,
    "root_id" Integer NOT NULL Default 0,
    "name"    Varchar(20)NOT NULL ,
    "status"   boolean Default 'true',
    "created" Timestamp Default current_timestamp,
    "modified" Timestamp Default current_timestamp,
    UNIQUE (id,root_id,name),
    PRIMARY KEY ("id")
--    FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
);
INSERT INTO btob.directory (id,root_id,name) VALUES (0,0,'/');
Alter table btob.directory add  FOREIGN KEY (root_id) REFERENCES btob.directory (id) ON DELETE CASCADE;
Create index "directory_index" on btob.directory using btree ("id","root_id","name");