SQL约束/触发器 - 是否可以编写一个约束，当“插入记录时它必须包含两个字段之一”？ [英] SQL Constraint/trigger - Is it possible to write a constraint that checks "when you Insert a record it must contain one of two fields"?

问题描述

可以对正在输入的记录具有约束/触发器，以检查用户是否已输入三个字段（所有字段都可以为空）中的至少一个。例如，我有一个数据库用于跟踪其他软件中的错误和新功能。当发现一个bug时，会创建一个Feature记录，它可以有三个外键：discoveredID，fixedID或newFeatureID，所有三个都可以为null（因为它可能是一个已发现的错误，已修复的错误或添加到软件中的新功能），但是用户在输入数据时必须选择至少一个。我可以使用我的数据库控制外部程序中的输入，但是如果有人编写程序，他们可能不会把那个检查到位，所以我想阻止他们这样做，并输入损坏的数据。

Is it possible to have a constraint/trigger on a record being entered that checks if the user has entered at least one of three fields (all of which can be null). For example, I have a database being used to track bugs and new features in other pieces of software. When a bug is discovered a Feature record is created which can have three foreign keys, discoveredID, fixedID or newFeatureID, all three can be null (because it could be a discovered bug, fixed bug or a new feature added to the software.) but the user must select at least one when entering data. I can control the input in an external program using my database but if someone else writes a program they may not put that check in place, so I want to stop them doing that and entering corrupt data.

推荐答案

使用SQL Server，添加表级检查约束就足够了。

Using SQL Server, adding a Table level check constraint would suffice.

CREATE TABLE Bugs (
  discoveredID INTEGER
  , fixedID INTEGER 
  , newFeatureID INTEGER  
  )

ALTER TABLE Bugs ADD CONSTRAINT CKC_AtLeastOne CHECK (COALESCE(discoveredID, fixedID, newFeatureID) IS NOT NULL)

INSERT INTO Bugs VALUES (NULL, NULL, 1)
INSERT INTO Bugs VALUES (NULL, 1, NULL)
INSERT INTO Bugs VALUES (1, NULL, NULL)
INSERT INTO Bugs VALUES (NULL, NULL, NULL) -- Fails

DROP TABLE Bugs

这篇关于SQL约束/触发器 - 是否可以编写一个约束，当“插入记录时它必须包含两个字段之一”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！