All geek questions in one place

MySQL: limiting the set of columns so that at least one is not NULL

I want to have a SQL table with two columns. One of them is the key to another table, the other is a string literal. The idea is that phone numbers can be entered exactly (in this case, the identifier is used in the phonebook table) or as a template (which uses a string literal).

This means that one column in the table will contain a value and the other will contain NULL.

Is it possible to restrict a table so that one column must have a value and the other should be NULL? If both columns are NULL or both are value, then the row is invalid.

I have a feeling that MySQL cannot do this (since it does not have complex tools when it comes to restrictions), but that does not hurt to ask.

+6
source share
2 answers

I do not know how to provide such a restriction.

As a workaround, you can consider two different columns: If you have one column for data containing a phone book identifier or a string literal, and another column for a data type is either "exact" or "wildcard", - you can set a NOT NULL constraint for both columns. One of the obvious drawbacks is that you can no longer have FK restrictions on the phone book table.

+4
source

You can fire triggers before inserting, check values, and determine if an insert or update will occur. A good example of creating such triggers can be found here: https://dba.stackexchange.com/questions/43284/two-nullable-columns-one-required-to-have-value

+2
source

Source: https://habr.com/ru/post/885498/

More articles:


All Articles