提要:通过本教程,你将学会如何使用 CREATE TABLE 语句来创建 PostgreSQL 数据表。

PostgreSQL 的 CREATE TABLE 语法

要在 PostgreSQL 创建表,需要使用 CREATE TABLE 语句。其语法如下:

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
) INHERITS existing_table_name;

来看一下该语法的细节:

  • 首先,在 CREATE TABLE 后面指定了要创建的数据表的名称。
  • 接下来,指定了字段名、数据类型和约束组成的字段列表。多个字段间用逗号分隔。约束定义了字段的规则,比如 NOT NULL
  • 然后,在字段列表后面定义了表级别的约束。用来定义整张表的规则。
  • 最后,指定了从一张已存在的表中继承。意思是,新表包含了已存在的表里的所有字段和在 CREATE TABLE 定义的新字段。

PostgreSQL 的字段约束

以下是 PostgreSQL 中常用的字段约束:

  • NOT NULL:字段的值不能为 NULL
  • UNIQUE:字段的值在整张表中,必须是唯一的。然而,该约束允许多个 NULL 值,因为 PostgreSQL 会把每个 NULL 视为唯一。注意,标准 SQL 只允许该字段里存在一个 NULL
  • PRIMARY KEY:主键。该约束是 NOT NULLUNIQUE 约束的组合。在字段级约束中,只能定义一个字段为 PRIMARY KEY。如果要定义包含多个字段的 PRIMARY KEY,需要使用表级约束。
  • CHECK:在插入或更新数据时启用条件检查。比如,商品表的“价格”字段的值必须是正数。
  • REFERENCES:约束字段的值必须存在于另一张表。使用 REFERNCES 来来定义外键约束(foreign key constraint)。

PostgreSQL 的表约束

表约束和字段约束类似,只不过表约束是应用于整张表,而字段约束只应用于特定的字段。

  • UNIQUE (column_list):强制存储在圆括号里的 column_list 字段列表的值是唯一的。
  • PRIMARY KEY (column_list):定义包含多个字段的主键。
  • CHECK (condition):插入或更新时检查条件。
  • REFERENCES:约束字段的值必须存在于另一张表。

PostgreSQL 的 CREATE TABLE 示例

我们将新建一张名为 account 的表。它的字段和相应的约束如下:

字段 约束
user_id PRIMARY KEY
username UNIQUE 和 NOT NULL
password NOT NULL
email UNIQUE 和 NOT NULL
created_on NOT NULL
last_login NULL

下面是创建该表的语句:

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);

接下来创建包含 role_idrole_name 2个字段的 role 表:

CREATE TABLE role(
 role_id serial PRIMARY KEY,
 role_name VARCHAR (255) UNIQUE NOT NULL
);

下面的语句用来创建 account_roles 表,它有三个字段:user_idrole_idgrant_data

CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

三张表及其关联关系如下:

CREATE TABLE

现在,来看看上面语句的细节:

account_role 表的主键包含两个字段:user_idrole_id,所以,我们使用了表级约束来定义这个复合主键:

PRIMARY KEY (user_id, role_id)

由于 user_id 字段引用了 account 表的 user_id 字段,所以需要定义一个外键:

CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

同样的,role_id 也需要定义一个外键:

CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
下一篇:PostgreSQL 修改表