如何为文本字段添加长度约束

Question

似乎在使用PostgreSQL（或其他支持它的数据库）时，最好使用TEXT数据类型而不是character varying(NN)，因为没有性能惩罚，并且可以通过删除和重新应用约束来调整最大可能长度，而不影响使用该字段的任何视图等。

但是，如何应用此约束（SQL代码）？

- Tobias

---

但是带有长度检查约束的文本列真的比nvarchar更有效吗？ - jarlh

@jarlh Postgres没有nvarchar。 - Vivek S.

@jarlh 请参考http://www.postgresql.org/docs/current/interactive/datatype-character.html。"提示：除了使用空格填充类型时增加的存储空间和在存储到长度受限列时检查长度所需的额外CPU周期之外，这三种类型之间没有性能差异。" - IMSoP

谢谢您提出这个问题，Tobias博士。 - user12861522

Answer 1

当您创建表格时，您可以进行以下操作之一：

CREATE TABLE names (
  name text CONSTRAINT namechk CHECK (char_length(name) <= 255)
)

(namechk仅是限制名称的名称)

对于ALTER TABLE同样适用：

ALTER TABLE names
  ADD CONSTRAINT namechk CHECK (char_length(name) <= 255);

- gmaliar

---

我使用 char_length 还是 length 有关系吗？我同时使用后者应用了约束... - Tobias

错误：在“namechk”附近有语法错误。看起来当您在表创建时指定约束名时，无法指定它。 - deFreitas

@deFreitas，您指的是哪个版本的PostgreSQL？ - gmaliar

在版本 PostgreSQL 10.3 中，CREATE TABLE names ( name text CHECK namechk (char_length(name) <= 255)) 不起作用，但是 CREATE TABLE names ( name text CHECK (char_length(name) <= 255)) 可以。 - deFreitas

@deFreitas，你的语法不正确。应该是CONSTRAINT namechk CHECK，而不是CHECK namechk (...)。 - Sampson Crowley

@Tobias char_length 会测量字符长度；length 会测量字节长度。https://dev59.com/o3I-5IYBdhLWcg3wq6Zo - vaer-k

Answer 2

这里有三个问题:

1. 使用 text + 检查约束更好，还是使用 varchar(N) 更好？
2. 如何编写适当的检查约束？
3. 应该命名约束，还是让系统自动分配名称？

答案:

1. 在查看模式和从其他数据库转移的开发人员所期望看到的方面上，varchar(N) 更明显。但是，正如您所说，稍后更改它会更困难。请记住，应用新的/修改的检查约束不是免费的 - 所有现有行都必须根据约束进行检查，因此在大型表上需要进行大量读取。
2. 检查约束的语法是 CONSTRAINT name CHECK (condition)（或者只是 CHECK (condition)，Postgres 本身会提供一个名称）在 CREATE TABLE 语句中，以及 ALTER TABLE table_name ADD CONSTRAINT name CHECK (condition);。 condition 将是使用适当的字符串函数的表达式，例如：char_length(foo) <= 255。
3. 为约束添加名称非常有用，如果您想稍后管理约束。特别是，由于您正在使用此功能以实现灵活性，因此您可能希望编写代码以删除并重新创建具有新长度的约束。如果您仅使用图形工具，则没有问题，但是如果可以脚本化更改，则管理多个服务器（例如开发、测试和生产副本）变得更加容易。使用命名约束，这将类似于 ALTER TABLE foo DROP CONSTRAINT ck_bar_length; ALTER TABLE foo ADD CONSTRAINT ck_bar_length CHECK ( char_length(bar) <= 100 ); 我实际上无法想出命名约束的缺点。

- IMSoP

---

当然，约束应该有一个名称；这就是我为什么说“使用像pgAdmin这样的工具添加”（它会构建一个遵循模式的名称，这可能是有用的）的原因。 - Tobias

@Tobias 任何工具都会根据某种算法创建一个名称。然而，在不同的地方和不同的时间运行时，不能保证使用相同的名称。老实说，我想不出不使用您自己的名称的理由。 - IMSoP

我不需要这样的保证。我会使用交互式工具一次，然后使用它生成的SQL代码在其他地方应用相同的更改（如果需要），或将其存储在文件中。 - Tobias

@Tobias 这就是为什么不选择一个名字并不重要的原因，但这并不是不选择名字的理由。我唯一能想到的不选择名字的理由就是为了节省10秒钟的打字时间，这个理由相当无聊。 - IMSoP

如果您始终使用相同的工具并让它命名事物，则无需担心重复现有名称。 - gerardw