結構化查詢語言/建立表

在資料庫開發週期中，基本步驟之一是確定表結構的決策。為此，可以使用 CREATE TABLE 語句，開發人員用它來定義表，以及它們的列和約束。

由於該命令可以啟用很多功能，它的語法稍微複雜一些。本頁面展示了最重要的部分。語法並不直觀。在某些情況下，可以使用替代方式表達相同的意思，例如，主鍵可以在列定義中定義為列約束，在命令末尾定義為表約束，或者作為獨立的命令 “ALTER TABLE ADD CONSTRAINT ...;” 。

CREATE TABLE <tablename> (
  <column_name> <data_type> <default_value> <identity_specification> <column_constraint>,
  <column_name> <data_type> <default_value>                          <column_constraint>,
  ...,
  <table_constraint>,
  <table_constraint>,
  ...
);

總體描述

在介紹性關鍵字 CREATE TABLE 後，指定表名。在括號內，列出列定義。每個列由它的名稱、資料型別、可選的預設值和該列的可選約束定義。

在列定義列表之後，開發人員可以指定表約束，例如主鍵和外部索引鍵、唯一條件和通用列條件。

第一個示例在頁面建立簡單表中展示，第二個示例在此展示。

CREATE TABLE test_table (
  -- define columns (name / type / default value / column constraint
  id             DECIMAL                           PRIMARY KEY,
  part_number    CHAR(10)          DEFAULT 'n/a'   NOT NULL,
  part_name      VARCHAR(500),
  state          DECIMAL           DEFAULT -1,
  -- define table constraints (eg: 'n/a' shall correlate with NULL)
  CONSTRAINT test_check CHECK ((part_number  = 'n/a' AND part_name IS     NULL) OR
                               (part_number != 'n/a' AND part_name IS NOT NULL))
);

該表包含 4 列。所有列都有資料型別，有些列有預設值。列 id 充當主鍵。表約束 test_check 保證如果記錄了 part_number，則 part_name 是必填項。

列定義

資料型別

標準定義了很多預定義的資料型別：固定長度和可變長度的字元型、字元大型物件 (CLOB)、固定長度和可變長度的二進位制型、二進位制大型物件 (BLOB)、數值型、布林型、日期時間型、間隔型、XML 型。除此之外，還有複雜型別，例如：行、引用 (引用)、陣列、多集和使用者定義型別 (UDT)。預定義資料型別在下一頁中解釋。為了簡化，本頁僅使用 CHAR、VARCHAR 和 DECIMAL。

預設值

列可以有預設值。它的資料型別與列的型別一致。它可以是常量值，例如數字 -1 或字串 'n/a'，也可以是系統變數或用於確定動態值的函式呼叫，例如使用者名稱或實際時間戳。

預設子句影響那些未指定列的 INSERT 和 MERGE 命令。在我們示例資料庫中，person 表的列 weight 的預設值為 0。如果在 INSERT 命令中省略該列，DBMS 將儲存值 0。

-- This INSERT command omits the 'weight' column. Therefore the value '0' (which is different from
-- the NULL value) is stored in the weight column.
INSERT INTO person (id, firstname, lastname,    date_of_birth,     place_of_birth, ssn)
VALUES             (11, 'Larry',   'Goldstein', date'1970-11-20', 'Dallas',        '078-05-1120');
COMMIT;

-- This SELECT retrieves the row ...
SELECT * 
FROM   person
WHERE  id = 11
AND    weight = 0;

-- ... but not this one:
SELECT * 
FROM   person
WHERE  id = 11
AND    weight IS NULL;

識別符號規範

識別符號規範 用於生成一系列唯一值，這些值充當錶行的主鍵。標準將語法定義為：“GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY”。不幸的是，大多數 DBMS 供應商不支援這種格式。相反，他們提供了不同的語法，甚至不同的概念來生成主鍵值。有些使用生成器/序列和觸發器的組合，另一些使用特殊資料型別，或者使用不同的關鍵字。

有關廣泛實施的概述，請參閱華夏公益教科書 SQL 方言參考：自動遞增列.

列約束

列約束子句指定所有值必須滿足的條件。有不同型別的列約束

NOT NULL

主鍵

唯一

外部索引鍵

檢查值

NOT NULL 短語定義不允許在該列中儲存 NULL 值。

-- The column col_1 is per definition not allowed to hold the NULL value
CREATE TABLE t1 (col_1 DECIMAL NOT NULL);

-- This INSERT command will fail
INSERT INTO t1(col_1) values(NULL);

-- The same applies to the following UPDATE command
INSERT INTO t1(col_1) values(5);
UPDATE t1 SET col_1 = NULL;

PRIMARY KEY 短語定義該列充當表的 Primary Key。這意味著不允許在該列中儲存 NULL 值，並且所有行的值彼此不同。

CREATE TABLE t2 (col_1 DECIMAL PRIMARY KEY);

-- This INSERT will fail because a primary key column is not allowed to store the NULL value.
INSERT INTO t2(col_1) VALUES(NULL);

-- This INSERT works
INSERT INTO t2(col_1) VALUES(5);

-- But the next INSERT will fail, because only one row with the value '5' is allowed.
INSERT INTO t2(col_1) VALUES(5);

UNIQUE 約束與 PRIMARY KEY 短語的含義類似。但有兩個細微的差別。

首先，UNIQUE 列的不同行的值不允許相等，這與 PK 相同。但它們允許包含 NULL 值，這與 Primary Key 不同。NULL 值的存在有一個含義。因為null = null 從不評估為true（它評估為unknown），所以可能存在多行在定義為 UNIQUE 的列中包含 NULL 值。

其次，每個表只允許一個主鍵定義。相反，可能存在多個 UNIQUE 約束（針對不同的列）。

CREATE TABLE t3 (col_1 DECIMAL UNIQUE);

-- works well
INSERT INTO t3(col_1) VALUES(5);
-- fails because there is another row with value 5
INSERT INTO t3(col_1) VALUES(5);

-- works well
INSERT INTO t3(col_1) VALUES(null);
-- works also
INSERT INTO t3(col_1) VALUES(null);

-- check the results
SELECT * FROM t3;

FOREIGN KEY 條件定義該列只能包含在另一個表（同一表或其他表）的不同列中儲存的值。該不同的列必須是 UNIQUE 或 Primary Key，而外部索引鍵列本身的值可以為多行包含相同的值。結果是，在另一個表中沒有包含完全相同值的行的記錄之前，不能建立包含某個值的該列的記錄。在我們示例資料庫中，我們有一個contact 表，它的列person_id 引用 persons 的 id。一個人在儲存相應的個人資訊之前不能儲存聯絡資訊是有意義的。

外部索引鍵是實現一對多 (1:m) 關係的技術。

-- A table with a column which refers to the 'id' column of table 'person'
CREATE TABLE t4 (col_1 DECIMAL REFERENCES person(id));

-- This INSERT works as in table 'person' of our example database there is a row with id = 3.
INSERT INTO t4(col_1) VALUES(3);

-- This statement will fail because in 'person' there is no row with id = 99.
INSERT INTO t4(col_1) VALUES(99);

列檢查檢查列的值，以檢視它們是否符合定義的條件。在這樣的列檢查中，只能看到實際的列。如果條件涵蓋兩個或多個列（例如，col_1 > col_2），則必須使用表檢查。

-- 'col_1' shall contain only values from 1 to 10.
-- A hint to MySQL users: MySQL before 8.0.16 accepts the syntax of column checks - but ignores the definitions silently. The same applies to MariaDB before 10.2.1.
CREATE TABLE t5 (col_1 DECIMAL CHECK (col_1 BETWEEN 1 AND 10));

-- This INSERT works:
INSERT INTO t5(col_1) VALUES(3);

-- This statement will fail:
INSERT INTO t5(col_1) VALUES(99);

表約束

表約束定義了對整個表強制執行的規則。它們的語義和語法部分與之前顯示的列約束重疊。

表約束定義在所有列定義之後。語法以關鍵字 CONSTRAINT 開頭，後跟可選名稱。以下示例包含可選名稱t6_pk、t6_ik 和t6_fk。建議包含名稱。如果出現錯誤異常，大多數 DBMS 會將此名稱作為相關錯誤訊息的一部分包含在內 - 如果未定義名稱，DBMS 可能會使用其內部命名約定，這可能很含糊。

主鍵、唯一鍵和外部索引鍵

與列約束部分中顯示的方式相同，主鍵、唯一鍵和外部索引鍵條件可以表示為表約束。語法略微不同於列約束語法；語義相同。

-- A table with a PK column, one UNIQUE column and a FK column.
CREATE TABLE t6 (
  col_1 DECIMAL,
  col_2 CHAR(10),
  col_3 DECIMAL,
  CONSTRAINT t6_pk PRIMARY KEY (col_1), -- 't6_pk' is the name of the constraint
  CONSTRAINT t6_uk UNIQUE      (col_2),
  CONSTRAINT t6_fk FOREIGN KEY (col_3) REFERENCES person(id)
);

NOT NULL 和簡單列檢查

類似於列約束部分，NOT NULL 條件和簡單列檢查可以表示為表表達式。

CREATE TABLE t7 (
  col_1 DECIMAL,
  col_2 DECIMAL,
  CONSTRAINT t7_col_1_nn    CHECK (col_1 IS NOT NULL),
  CONSTRAINT t7_col_2_check CHECK (col_2 BETWEEN 1 and 10)
);

一般列檢查

如果條件影響多於一列，則必須將其表示為表約束。

CREATE TABLE t8 (
  col_1 DECIMAL,
  col_2 DECIMAL,
  col_3 DECIMAL,
  col_4 DECIMAL,
  -- col_1 can hold only those values which are greater than col_2 
  CONSTRAINT t8_check_1 CHECK (col_1 > col_2),
  -- If col_3 is NULL, col_4 must be NULL also
  CONSTRAINT t8_check_2 CHECK ((col_3 IS     NULL AND col_4 IS     NULL) OR
                               (col_3 IS NOT NULL AND col_4 IS NOT NULL))
);

-- These two INSERTs work as they meet all conditions
INSERT INTO t8 VALUES(1, 0, null, null);
INSERT INTO t8 VALUES(2, 0, 5, 5);

-- Again: MySQL ignores check conditions silently

-- This INSERT fails because col_1 is not greater than col_2
INSERT INTO t8 VALUES(3, 6, null, null);

-- This INSERT fails because col_3 is not null and col_4 is null
INSERT INTO t8 VALUES(4, 0, 5, null);

列約束與表約束

如您所見，某些約束可以作為列定義的一部分定義，稱為列約束，或作為單獨的表約束定義。表約束有兩個優勢。首先，它們功能更強大一些。

其次，它們有自己的名稱！這有助於理解系統訊息。此外，它打開了在表存在幷包含資料後管理約束的可能性。ALTER TABLE 語句可以停用、啟用或刪除約束。為此，您必須知道它們的名稱。

清理

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
DROP TABLE t6;
DROP TABLE t7;
DROP TABLE t8;

練習

建立一個名為“company”的表，包含列“id”（數字型別，主鍵）、“name”（最大長度為 200 的可變大小字串）、“isin”（長度為 12 的字串，不可為空，唯一值）。
使用列約束和表約束分別建立一個解決方案。

點選檢視解決方案

-- column constraints only
CREATE TABLE company_1 (
  id   DECIMAL PRIMARY KEY,
  name VARCHAR(200),
  isin CHAR(12) NOT NULL UNIQUE
);
-- table constraints only
CREATE TABLE company_2 (
  id   DECIMAL,
  name VARCHAR(200),
  isin CHAR(5),
  CONSTRAINT company_2_pk PRIMARY KEY (id),
  CONSTRAINT company_2_uk UNIQUE      (isin),
  CONSTRAINT company_2_check_isin CHECK (isin IS NOT NULL)
);

建立一個名為“accessory”的表，包含列“id”（數字型別，主鍵）、“name”（最大長度為 200 的可變大小字串，唯一）、“hobby_id”（十進位制型別，不可為空，外部索引鍵到表“hobby”的列“id”）。
使用列約束和表約束分別建立一個解決方案。

點選檢視解決方案

-- column constraints only
CREATE TABLE accessory_1 (
  id       DECIMAL PRIMARY KEY,
  name     VARCHAR(200) UNIQUE,
  hobby_id DECIMAL NOT NULL REFERENCES hobby(id)
);
-- table constraints only
CREATE TABLE accessory_2 (
  id       DECIMAL,
  name     VARCHAR(200),
  hobby_id DECIMAL,
  CONSTRAINT accessory_2_pk PRIMARY KEY (id),
  CONSTRAINT accessory_2_uk UNIQUE      (name),
  CONSTRAINT accessory_2_check_1  CHECK (hobby_id IS NOT NULL),
  CONSTRAINT accessory_2_fk FOREIGN KEY (hobby_id) REFERENCES hobby(id)
);

-- Test some legal and illegal values
INSERT INTO accessory_1 VALUES (1, 'Fishing-rod', 2);
COMMIT;
-- ...

COMMIT 和 ROLLBACK

結構化查詢語言
建立表

資料型別