May 17, 2010 at 12:26 pm
I have to believe this question has been asked, and answered, probably many times. But, I can't seem to find the right combination of keywords to find anything useful on the matter. So, here goes. Thanks for any help you can provide.
I'd like to enforce that application code populate one, and only one, of two fields in a table.
In this case, it is an inventory database. In an InventoryRequest table, I'd like to enforce that application code insert a value in either the StandardInventoryID field, or the SerializedInventoryID field (both integer foreign keys). One of them must be populated, but never both.
I subscribe to the school of thought that a frontend exists to violate a backend. Well, not really...I've seen it put that way for humor's sake - I do like to validate as much as possible on the backend, however. This frees me to write less cluttered frontend code.
I assume that a Trigger would do the job, and that using one is the recommended route. If this is correct, could someone please give me some guidance how I might write one to perform the described validation scenario?
On the other hand, one could normalize this scenario into more tables. I don't like to over-normalize, but if it is recommended by an expert or three, I'm certainly willing to go that route. I am not an expert SQL developer, by any means. In this project, however, that is my role (in addition to building the frontend).
This project is a Winforms application.
Many thanks.
May 17, 2010 at 1:47 pm
vba-dev (5/17/2010)
I have to believe this question has been asked, and answered, probably many times. But, I can't seem to find the right combination of keywords to find anything useful on the matter. So, here goes. Thanks for any help you can provide.I'd like to enforce that application code populate one, and only one, of two fields in a table.
In this case, it is an inventory database. In an InventoryRequest table, I'd like to enforce that application code insert a value in either the StandardInventoryID field, or the SerializedInventoryID field (both integer foreign keys). One of them must be populated, but never both.
I subscribe to the school of thought that a frontend exists to violate a backend. Well, not really...I've seen it put that way for humor's sake - I do like to validate as much as possible on the backend, however. This frees me to write less cluttered frontend code.
I assume that a Trigger would do the job, and that using one is the recommended route. If this is correct, could someone please give me some guidance how I might write one to perform the described validation scenario?
On the other hand, one could normalize this scenario into more tables. I don't like to over-normalize, but if it is recommended by an expert or three, I'm certainly willing to go that route. I am not an expert SQL developer, by any means. In this project, however, that is my role (in addition to building the frontend).
This project is a Winforms application.
Many thanks.
Personally, I would avoid the trigger. 😀
Just a thought, but .....
couldn't you modify the stored procedure that is used to do the insert into the table so that it accepts one parameter and it's "name". In other words something like the below code.....
ALTER PROCEDURE dbo.insertIntoYourTable
(@InventoryID INT
,@fieldType CHAR(10) = 'standard')
AS
INSERT dbo.yourTable (a, b, c, StandardInventoryID,SerializedInventoryID)
SELECT
a
,b
,c
,StandardInventoryID = CASE WHEN @fieldType = 'standard' THEN @InventoryID END
,SerializedInventoryID = CASE WHEN @fieldType = 'serialized' THEN @InventoryID END
this is a SIMPLIFIED example!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 17, 2010 at 2:04 pm
Would a CHECK constraint help?
ALTER TABLE YourTable
ADD CONSTRAINT chkCol1Col2 CHECK ( ISNULL(col1,col2) IS NOT NULL );
This constraint would allow you to insert a NULL value either for Col1 or Col2 but not for both.
May 17, 2010 at 2:18 pm
Ah, hah!
I new there had to be some cool way to do this. Both suggestions are great.
All that's left is to choose one. I think I like the constraint route. I will be using metadata to generate business objects and validation routines in those objects. It's not clear to me just yet how I will translate the suggested constraint so that my validation plan can consume the metadata, but I have a feeling this route is the way to go for me.
Many thanks to you both. Excellent work!
May 17, 2010 at 2:38 pm
I tested the constraint method. It enforces part of the rule; that only one of the two fields can be null...but it doesn't prevent me from entering a (non-null) value into both fields. Still, it seems quite useful in situations for which it is suited.
I'll give the stored procedure a swing.
May 17, 2010 at 3:00 pm
I'm sorry!! I missed the second part of the requirement...
Here's the expanded constraint:
ALTER TABLE #temp
ADD CONSTRAINT chkCol1Col2 CHECK ( (ISNULL(col1,col2) IS NOT NULL) AND (col1 + col2 IS NULL));
It benefits from the fact that a unknown (NULL) value added to another value will return unknown (NULL) as long as CONCAT_NULL_YIELDS_NULL is OFF.
May 17, 2010 at 3:06 pm
Another option not depending on the CONCAT_NULL_YIELDS_NULL setting would be using (col1 IS NULL OR col2 IS NULL) instead of (col1 +col2) IS NULL...
May 17, 2010 at 3:47 pm
this works and is perhaps easier to read?
ALTER TABLE YourTable
ADD CONSTRAINT ck_oneorother CHECK ( (col1 is null and col2 is not null) OR (col1 is not null and col2 is null));
---------------------------------------------------------------------
May 17, 2010 at 3:49 pm
i had done this requirement before, but additionally one of the values had to be populated...i didn't see if both values were allowed to be null or not;
the only way i could think of doing this was with a check constraint and a user defined function.
here's how i did it:
create function ThereCanBeOnlyOne(@FirstFK as int,@SecondFK as int)
RETURNS int
AS
BEGIN
DECLARE @results int
IF @FirstFK IS NULL AND @SecondFK IS NULL
SET @results = 1
IF @FirstFK IS NULL AND @SecondFK IS NOT NULL
SET @results = 0
IF @FirstFK IS NOT NULL AND @SecondFK IS NULL
SET @results = 0
IF @FirstFK IS NOT NULL AND @SecondFK IS NOT NULL
SET @results = 1
return @results
END
GO
CREATE TABLE [dbo].[PARENT] (
[PARENTID] INT IDENTITY(1,1) NOT NULL,
[PARENTTEXT] VARCHAR(30) NULL,
CONSTRAINT [PK__parent__1332DBDC] PRIMARY KEY CLUSTERED (parentid))
CREATE TABLE [dbo].[CHILD] (
[CHILDID] INT IDENTITY(1,1) NOT NULL,
[FIRSTFK] INT NULL,
[SECONDFK] INT NULL,
CONSTRAINT [CK__ONLYONEFKALLOWED] CHECK ([dbo].[ThereCanBeOnlyOne]([FIRSTFK],[SECONDFK])=(0)),
CONSTRAINT [FK__child__FIRSTFK__17F790F9] FOREIGN KEY (FIRSTFK) REFERENCES parent(parentid),
CONSTRAINT [FK__child__SECONDFK__18EBB532] FOREIGN KEY (SECONDFK) REFERENCES parent(parentid))
Lowell
May 17, 2010 at 4:26 pm
imu92, thanks for the update.
George, thanks also for the simplification.
After studying both statements, I realized that I should have been able to write this constraint without posting. The logic is elementary.
Lowell, my requirement is also that one of the two fields be populated. The constraint offered by imu92 enforces it.
Thanks to all. I really appreciate the guidance. I learned a lot from incorporating your comparison statements into a stored procedure also, Jason.
May 17, 2010 at 4:32 pm
Lowell,
I took a close look at your function and its usage. Pretty nice. From the standpoint of code re-use, I think it's quite valuable. If the scenario in my original post presents itself repeatedly in my design, I'll probably steal it. 😉
Thanks very much for sharing it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply