结构化查询语言/创建表

在数据库开发周期中，基本步骤之一是确定表结构的决策。为此，可以使用 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

结构化查询语言
创建表

数据类型