开发者

Constraint to check if a column is null or references other column?

开发者 https://www.devze.com 2023-03-23 19:55 出处:网络
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

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 Languag开发者_开发技巧es (languageCode)


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).

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号