MySQL：约束一组列，因此至少有一列不为NULL [英] MySQL: Constraining a set of columns so at least one is not NULL

问题描述

我想有一个包含两列的SQL表。一个是进入另一个表的键，另一个是字符串字面量。这个想法是可以准确地输入电话号码（在这种情况下，使用电话簿表中的ID）或者作为通配符（使用字符串文字）。

I want to have a SQL table with two columns. One is a key into another table, the other holds a string literal. The idea is phone numbers can be entered either exactly (in which case the ID into the phonebook table is used) or as a wildcard (in which the string literal is used).

这意味着表中的一列将保存一个值，另一列将保留NULL。

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

是否可以通过这样的方式约束表：一列必须具有值，另一列必须为NULL？如果两个列都为NULL或两者都有值，则该行无效。

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

我有一种感觉，MySQL不能这样做（因为它似乎没有一个全面的工具箱，当涉及到约束），但它不能伤害要问。

I have a feeling that MySQL can't do this (as it doesn't seem to have a comprehensive toolbox when it comes to constraints), but it couldn't hurt to ask.

推荐答案

我不知道如何强制这样的约束。

I am not aware of a way to enforce such a constraint.

作为解决方法，您可以考虑有两个不同的列：如果您有一列数据 - 包含电话号码id或字符串字面值，另一列数据类型 - 精确或通配符，可以为两列设置NOT NULL约束。一个明显的缺点是你不能对电话簿表有FK约束。

As a workaround, you may consider to have two different columns: If you have one column for the data - containing the phonebook id or the string literal, and another column for the data type - either 'exact' or 'wildcard' -, you can set a NOT NULL constraint to both columns. One obvious drawback is that you cannot have a FK constraint to the phonebooks table any more.

这篇关于MySQL：约束一组列，因此至少有一列不为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！