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.
views:
29answers:
2
+3
A:
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
Lieven
2010-08-25 11:05:32
Thanks for the fast response, this is just what i needed!
David Swindells
2010-08-25 12:12:24
+2
A:
Reading your design got me thinking that maybe the problem could be in the table design more than the requirement to create a Constraint/Trigger.
For example, you mention that you can have 3 types of bug (discovered bug, fixed bug or a new feature)
Would it be possible to have a composite key of two fields which would be the bug type, with the assocaited ID field next to it.
So instead of: discoveredID, fixedID, newFeatureID
you'd have just: bugTypeID, BugID
With this change you could just concentrate on the bugTypeID being 1,2 or 3.
kevchadders
2010-08-25 11:11:17
+1. Coming to think of it, by correctly modeling the data model, there's no need for a check constraint. Furthermore, it would be easy to add a new type of bug.
Lieven
2010-08-25 11:14:11
The specs say "the user must select at least one when entering data". The proposed super-over-normalized table design solves the case "the user must select only one". The proposal might be right, or not... and the "answer" does not address the question that was actually asked... that's rather impolite IMO :)
Breck Carter
2010-08-25 11:57:56
You're right, although i've done a little tweaking with the design and i still need the solution but somewhere else in my new design. I suppose i could remove the need for it in my design but it's a case of usable design given the time constraints beats perfect design unfortunately. I will review it in future and bear your points in mind though. Thanks for your contribution.
David Swindells
2010-08-25 12:12:07
@Breck - It wasnt meant to be Impolite and i hope the OP didnt see it that way. It does stear away from the OP question but i felt it was worth mentioning, so that he could consider another solution to his problem
kevchadders
2010-08-25 12:19:47
Also this design lends itself nicely to the OP wanting to increase the num of bugs types without effect GUI/database design as all he has to do is create additional entries into a description table for the Bug Ids. I beleive this solution does answer the question by eliminating the need for the Triggers in the first place.
kevchadders
2010-08-25 12:24:02
The only problem with this is that you can't add multiple bugtypeID's, i need fixed and discovered so i can see which user fixed and discovered the bug, incase the fix is wrong, then i can undo the changes by asking that user to or by using the description they left to. There are floors in my design but it'll do for now. Thanks for your answer tho.
David Swindells
2010-08-25 13:07:52