检查列是否为空或引用其他列的约束？
是否可以（在 PostgreSQL 中）制定一个约束，规定某列必须为空或包含另一个表中另一列的值？换句话说，将 CHECK 约束与 FOREIGN KEY 约束结合起来？
我想定义的是该列应该为空或包含来自另一列的值。本例的目的是检查用户选择的语言是否在支持的语言列表中，或者是否未设置（保留为空）。
所以这行中的一些东西（这是行不通的）：
ALTER TABLE MyTable ADD CONSTRAINT my_constraint
CHECK (languageCode IS NULL) OR (languageCode) REFERENCES Languages (languageCode)
Is it possible (in PostgreSQL) to make a constraint that says that a column must be null or contain a value from another column, in another table? In other words, to combine a CHECK constraint with a FOREIGN KEY constraint?
What I would like to define is that the column should be null or contain a value from another column. The purpose in this case is to check that a user selected language is amongst a list of supported languages, or not set (left as null).
So something in the lines of this (which doesn't work):
ALTER TABLE MyTable ADD CONSTRAINT my_constraint
CHECK (languageCode IS NULL) OR (languageCode) REFERENCES Languages (languageCode)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据 MarcB 的评论：可空列上的外键约束将按照您的要求执行。
然而，一般来说，强制执行“复杂”约束的最常见方法是创建一个带有逻辑的标量函数（返回布尔值）并检查该函数（传入要检查的值，或表的 PK，或任何适合的内容）您的需求）。
As per MarcB's comment: A Foreign Key Constrain on a NULLable column will do just what you asked.
In general, however, the most common way to enforce 'complex' constraints is to create a scalar function with the logic (that returns a boolean) and CHECK that function (passing in the value to check, or the table's PK, or whatever suits your needs).