SQL • 無限: PostgreSQL 修改表格語法(二)

目標說明

表格建立的SQL語法，請參考這一篇。
表格修改的SQL語法，請參考這一篇。
本篇著重於主鍵、外來健的新增、修改與刪除。

主健、外來健新增的方式如下：

CREATE TABLE table_name (
   column_name data_type column_constraint table_constraint,
   [ ... ]
)

其中 column_constraint 是:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

並且 table_constraint 是:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH 運算子 [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

主健、外來健修改的方式如下：

ALTER TABLE action [, ... ];

其中 action 是:
    ADD [ COLUMN ] column_name data_type CHECK column_constraint
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    DROP CONSTRAINT column_constraint

並且 table_constraint_using_index 是:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

修改 score 表內的 student_id 為 Primary key：

student=# ALTER TABLE score ADD PRIMARY KEY (student_id);
注意:  ALTER TABLE / ADD PRIMARY KEY 將會建立隱含索引 "score_pkey" (適用於資料表 "score")
ALTER TABLE
student=#

將 score 表內的 student_id 關聯至 baseData表格內的 id：

student=# ALTER TABLE score ADD FOREIGN KEY (student_id) REFERENCES basedata(id);
ALTER TABLE

查詢 score 表格目前情狀：

student=# \d score
            資料表 "public.score"
    欄位    |         型別          | 修飾詞
------------+-----------------------+---------
 student_id | character varying(11) | 非 Null
 subject_id | character varying(11) |
 score      | integer               |
索引:
    "score_pkey" PRIMARY KEY, btree (student_id)
檢查條件約束
    "score_check" CHECK (score >= 0 AND score <= 100)
外鍵條件約束:
    "score_student_id_fkey" FOREIGN KEY (student_id) REFERENCES basedata(id)

student=#

刪除 score 表格主鍵：

student=# ALTER TABLE score DROP CONSTRAINT "score_pkey";
ALTER TABLE

新增成績資料到 score 表格：

student=# INSERT INTO score(student_id,subject_id,score)
student-# VALUES('S0001','AT0001',89);
INSERT 0 1
student=# INSERT INTO score(student_id,subject_id,score)
student-# VALUES('S0001','AT0002',90);
INSERT 0 1

查詢 score 資料表內容:

student=# SELECT * FROM score;
 student_id | subject_id | score
------------+------------+-------
 S0001      | AT0001     |    89
 S0001      | AT0002     |    90
(2 筆資料列)

新增不存在的學生成績資料到 score 表格：

student=# INSERT INTO score(student_id,subject_id,score)
VALUES('S0005','AT0002',90);
錯誤:  資料表 "score" 的插入或更新違反外鍵限制 "score_student_id_fkey"
詳細資料: 索引鍵 (student_id)=(S0005) 沒有出現在資料表 "basedata"。
student=#

2016年7月11日 星期一

PostgreSQL 修改表格語法(二)

2016年7月11日星期一