结构化查询语言(Structured Query Language): 创建表

数据库表结构的定义与创建

在数据库开发中，定义表结构是一个基础步骤。通过 CREATE TABLE 语句，开发人员可以定义表的列及其约束条件。由于该命令可以激活许多功能，其语法较为复杂。以下是该命令的关键内容及示例。

基本语法

CREATE TABLE <表名> (
  <列名> <数据类型> <默认值> <标识规范> <列约束>,
  <列名> <数据类型> <默认值>               <列约束>,
  ...,
  <表约束>,
  <表约束>,
  ...
);

说明：

表名：使用 CREATE TABLE 定义表名。
列定义：在圆括号中列出列名、数据类型、默认值和列约束。
表约束：定义表的约束条件，例如主键（Primary Key）、外键（Foreign Key）或唯一性条件（Unique）。

示例

示例 1：创建表并添加列和表约束

CREATE TABLE test_table (
  -- 定义列（名称 / 类型 / 默认值 / 列约束）
  id             DECIMAL                           PRIMARY KEY,
  part_number    CHAR(10)          DEFAULT 'n/a'   NOT NULL,
  part_name      VARCHAR(500),
  state          DECIMAL           DEFAULT -1,
  -- 定义表约束
  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 被定义为主键（Primary Key）。
表约束 test_check 确保当 part_number 值为 'n/a' 时，part_name 必须为空（NULL）。

列定义详解

1. 数据类型

SQL 标准定义了许多数据类型，包括：

字符串类型：CHAR（固定长度）、VARCHAR（可变长度）、CLOB（大对象）。
数字类型：DECIMAL、INTEGER 等。
日期时间类型：DATE、TIME。
布尔类型：BOOLEAN。

2. 默认值

列可以指定默认值，类型需与列的数据类型一致。默认值可以是：

常量值：如 -1 或 'n/a'。
系统变量：如当前时间 CURRENT_DATE。

示例：为表 person 的 weight 列设置默认值 0。

-- 插入时未指定 weight 列，因此存储默认值 0
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;

-- 验证插入结果
SELECT * FROM person WHERE id = 11 AND weight = 0; -- 有结果
SELECT * FROM person WHERE id = 11 AND weight IS NULL; -- 无结果

3. 标识规范

标识规范（Identity Specification）用于生成一系列唯一值，通常作为主键（Primary Key）。SQL 标准的语法为：

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY

但不同 DBMS 实现方式不同，有的使用生成器（Generators）或触发器（Triggers），有的使用特殊数据类型。

4. 列约束

列约束定义列值必须满足的条件。常见的约束类型有：

NOT NULL：禁止存储 NULL 值。
Primary Key：定义主键。
Unique：定义唯一约束。
Foreign Key：定义外键。
Check：检查值的合法性。

示例：定义 NOT NULL 约束。

-- 定义列不允许存储 NULL 值
CREATE TABLE t1 (col_1 DECIMAL NOT NULL);

-- 插入 NULL 会失败
INSERT INTO t1(col_1) VALUES(NULL);

-- 更新为 NULL 也会失败
INSERT INTO t1(col_1) VALUES(5);
UPDATE t1 SET col_1 = NULL;

总结

通过 CREATE TABLE 语句可以灵活地定义表结构，包括列、数据类型、默认值和约束等内容。以下是使用 CREATE TABLE 时的注意事项：

列定义与表约束：优先在列定义中添加常用约束，复杂的约束使用表约束。
默认值：合理使用默认值可以减少数据插入时的操作。
NOT NULL 约束：保证数据完整性，避免存储不合法值。

以上为表结构设计和定义的关键知识点及其示例演示。

主键、唯一约束、外键及检查约束

以下是关于 PRIMARY KEY、UNIQUE、FOREIGN KEY 和 CHECK 约束的详细说明及示例。

主键（PRIMARY KEY）

主键的特点：

主键列不能包含 NULL 值。
主键列的所有行值必须唯一。
每个表只能定义一个主键。

示例：

CREATE TABLE t2 (col_1 DECIMAL PRIMARY KEY);

-- 失败，因为主键列不允许存储 NULL 值
INSERT INTO t2(col_1) VALUES(NULL);

-- 成功
INSERT INTO t2(col_1) VALUES(5);

-- 失败，因为主键列的值必须唯一
INSERT INTO t2(col_1) VALUES(5);

唯一约束（UNIQUE）

UNIQUE 的特点：

列值必须唯一，但允许存储 NULL 值。
多个 NULL 值被视为不相等，因此可以同时存在多行 NULL 值。
一个表可以定义多个 UNIQUE 约束。

示例：

CREATE TABLE t3 (col_1 DECIMAL UNIQUE);

-- 成功
INSERT INTO t3(col_1) VALUES(5);

-- 失败，因为已经存在值为 5 的行
INSERT INTO t3(col_1) VALUES(5);

-- 成功插入 NULL 值
INSERT INTO t3(col_1) VALUES(NULL);

-- 允许再次插入 NULL 值
INSERT INTO t3(col_1) VALUES(NULL);

-- 检查结果
SELECT * FROM t3;

外键（FOREIGN KEY）

外键的特点：

外键列的值必须存在于另一个表的指定列中（通常是该表的主键或唯一列）。
外键列本身可以包含重复值。
外键用于实现一对多（1:m）关系。

示例：

-- 定义表并设置外键约束，引用 person 表的 id 列
CREATE TABLE t4 (col_1 DECIMAL REFERENCES person(id));

-- 成功，因为 person 表中存在 id 为 3 的行
INSERT INTO t4(col_1) VALUES(3);

-- 失败，因为 person 表中不存在 id 为 99 的行
INSERT INTO t4(col_1) VALUES(99);

检查约束（CHECK）

CHECK 的特点：

用于检查列值是否满足指定条件。
如果条件涉及多列，则需要使用表级约束。
一些 DBMS（如 MySQL 在 8.0.16 之前的版本）可能会忽略 CHECK 约束。

列级检查示例：

-- 定义列值范围为 1 到 10 的约束
CREATE TABLE t5 (col_1 DECIMAL CHECK (col_1 BETWEEN 1 AND 10));

-- 成功
INSERT INTO t5(col_1) VALUES(3);

-- 失败，因为值超出范围
INSERT INTO t5(col_1) VALUES(99);

表级检查示例：

CREATE TABLE t8 (
  col_1 DECIMAL,
  col_2 DECIMAL,
  col_3 DECIMAL,
  col_4 DECIMAL,
  -- col_1 必须大于 col_2
  CONSTRAINT t8_check_1 CHECK (col_1 > col_2),
  -- 如果 col_3 为 NULL，则 col_4 必须为 NULL
  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))
);

-- 满足条件，插入成功
INSERT INTO t8 VALUES(1, 0, NULL, NULL);
INSERT INTO t8 VALUES(2, 0, 5, 5);

-- 不满足条件，插入失败
INSERT INTO t8 VALUES(3, 6, NULL, NULL); -- col_1 <= col_2
INSERT INTO t8 VALUES(4, 0, 5, NULL);    -- col_3 非 NULL，但 col_4 为 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)
);

清理示例表

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

通过以上约束定义和示例，可以有效提高数据库的完整性与一致性。

Last modified: Tuesday, 28 January 2025, 1:48 PM