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).
精彩评论