December 11, 2014 at 6:33 am
This is obviously doable by restricting adds to stored procedures, but I'd like to do it with a constraint, or index or some other such construct "built in" to the structure, rather than relying on code to run.
I have three fields of concern - a required letter and a number, and an additional optional letter. Records must be unique across these three fields, which I handle with an index - no problem. However, it is permissible for there to be a record that has only a letter and number, OR for there to be one or more records that all have the same letter and number, and unique additional letter, but NOT both.
Once there is a record with only letter and number, trying to add a record with a letter, number and additional letter should fail.
Likewise, once there is at least one record with letter, number and additional letter, trying to add a record with only letter and number should fail.
e.g.
Okay
E 41
Okay
E 41 a
E 41 b
E 41 c
NOT Okay
E 41
E 41 a
E 41 b
E 41 c
Can anyone think of a way to define this restriction as a constraint, index or something similar?
December 11, 2014 at 6:41 am
What is the business need to use a schema object to prevent this?
December 11, 2014 at 6:45 am
Brandie Tarvin (12/11/2014)
What is the business need to use a schema object to prevent this?
I didn't specify schema, and in fact, have no idea how a schema could be used to accomplish it. If you know of a way, I'd like to hear it. The business need is that it is not acceptable information. An item in inventory is either specified as a whole, or its individual components are listed separately, but not both.
December 11, 2014 at 6:56 am
pdanes (12/11/2014)
Brandie Tarvin (12/11/2014)
What is the business need to use a schema object to prevent this?I didn't specify schema, and in fact, have no idea how a schema could be used to accomplish it. If you know of a way, I'd like to hear it. The business need is that it is not acceptable information. An item in inventory is either specified as a whole, or its individual components are listed separately, but not both.
Indexes, Constraints, etc. are all schema objects. I don't understand why the business would require using them for this particular situation. Could you please enlighten me?
December 11, 2014 at 7:13 am
Brandie Tarvin (12/11/2014)
pdanes (12/11/2014)
Brandie Tarvin (12/11/2014)
What is the business need to use a schema object to prevent this?I didn't specify schema, and in fact, have no idea how a schema could be used to accomplish it. If you know of a way, I'd like to hear it. The business need is that it is not acceptable information. An item in inventory is either specified as a whole, or its individual components are listed separately, but not both.
Indexes, Constraints, etc. are all schema objects. I don't understand why the business would require using them for this particular situation. Could you please enlighten me?
The business doesn't require a particular methodology. -I- want to do it that way. If I write stored procedures that check for this, someone else can write a different stored procedure, or even direct access, that doesn't. It's even possible that I would forget about the need for this restriction sometime in the future, and commit such an act myself. If I encode the restriction directly in the structure, nobody, including me, can get by it without specifically altering that restriction. This is much less likely to happen.
December 11, 2014 at 7:19 am
As much as it pains me to say this (because it could cause performance issues), I think your best bet is to put in an AFTER trigger to rollback the change if the recent insert violates your business rules.
December 11, 2014 at 7:27 am
Brandie Tarvin (12/11/2014)
As much as it pains me to say this (because it could cause performance issues), I think your best bet is to put in an AFTER trigger to rollback the change if the recent insert violates your business rules.
Thank you, a trigger occurred to me as well, but I was hoping someone sharper in SQL Server than I am might know of a better way. This database doesn't get hammered very hard, and reads are much more frequent than updates, so I doubt if performance would be an issue.
Triggers are certainly capable of causing problems, but I don't agree that they should never be used. They're an extremely useful tool in some situations, and this may be just such a place.
Thanks for looking at the problem.
December 11, 2014 at 7:32 am
Hi
Check constraint with user defined function should work as well
December 11, 2014 at 7:44 am
Emil Bialobrzeski (12/11/2014)
HiCheck constraint with user defined function should work as well
Hm, that's an interesting thought, but how would you code it? The function would need the infro from the record I'm trying to add, say E and 41, to see if E 41 (%) already exists, or E and 41 and a, to see if E 41 (blank) already exists. It could detect an unacceptable combination once it's already in the table, but I don't see how to code such a function to prevent the combination from arising in the first place.
December 11, 2014 at 7:50 am
Don't be to harsh on me i just done it quickly while doing my other work. In the example below i'm assuming that if the optional letter does not exist there is a NULL in place. Unique row identifier (ID) is also required.
CREATE TABLE Test (ID INT IDENTITY(1,1), Letter CHAR(1), Number INT, OptionalLetter CHAR(1))
GO
CREATE FUNCTION dbo.CKTest (@ID INT, @Letter CHAR(1), @Number INT, @OptionalLetter CHAR(1))
RETURNS BIT
AS
BEGIN
DECLARE @r BIT
DECLARE @IDF INT
SELECT @IDF = ID FROM Test WHERE ID != @ID AND Letter = @Letter AND Number = @Number AND OptionalLetter IS NULL
IF @IDF IS NOT NULL
SET @r = 1
ELSE IF @IDF IS NULL
BEGIN
SELECT @IDF = ID FROM Test WHERE ID != @ID AND Letter = @Letter AND Number = @Number AND ISNULL(OptionalLetter,'') = ISNULL(@OptionalLetter,OptionalLetter)
IF @IDF IS NOT NULL
SET @r = 1
ELSE
SET @r = 0
END
RETURN @r
END
GO
ALTER TABLE Test ADD CONSTRAINT CK_Test CHECK (dbo.CKTest(ID, Letter, Number, OptionalLetter) = 0)
GO
December 11, 2014 at 8:03 am
Emil Bialobrzeski (12/11/2014)
Don't be to harsh on me i just done it quickly while doing my other work. In the example below i'm assuming that if the optional letter does not exist there is a NULL in place.
My apologies, I meant how does a function in a check constraint get the parameters from the record that I'm trying to add? I know how to code a lookup, given the parameters, but when I simply try to add a record, and I want the check constraint to prevent me from doing so, how does the check constraint get the info needed to do a lookup, in this case, the parameters @Letter, @Number and @OptionalLetter, from the incoming record?
December 11, 2014 at 8:09 am
Yes the parameters are from the incoming record while you do an insert
ALTER TABLE Test ADD CONSTRAINT CK_Test CHECK (dbo.CKTest(ID, Letter, Number, OptionalLetter) = 0)
EDIT:
Use the code from my previous post, make some inserts to Test table, I just tested it and works as expected (i think :hehe:)
December 11, 2014 at 8:18 am
Emil Bialobrzeski (12/11/2014)
Yes the parameters are from the incoming record while you do an insert
ALTER TABLE Test ADD CONSTRAINT CK_Test CHECK (dbo.CKTest(ID, Letter, Number, OptionalLetter) = 0)EDIT:
Use the code from my previous post, make some inserts to Test table, I just tested it and works as expected (i think :hehe:)
Ok, I've got to get out of here right now, but I'll try it and let you know. Thanks.
December 11, 2014 at 3:11 pm
Here's a simplified function with sample testing data and results:
IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test
CREATE TABLE dbo.Test (letter char(1), number int, optional_letter char(1))
GO
IF OBJECT_ID('dbo.Test__FN_optional_letter') IS NOT NULL
DROP FUNCTION dbo.Test__FN_optional_letter
GO
CREATE FUNCTION dbo.Test__FN_optional_letter (@letter char(1), @number int, @optional_letter char(1))
RETURNS BIT
AS
BEGIN
RETURN (
SELECT CASE
WHEN @optional_letter = ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter <> '')
THEN -1
WHEN @optional_letter > ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter = ' ')
THEN -1
--?WHEN @optional_letter IS NULL AND ...?
ELSE 0
END
)
END
GO
ALTER TABLE dbo.Test ADD CONSTRAINT Test__CK_optional_letter CHECK(dbo.Test__FN_optional_letter(letter, number, optional_letter) = 0)
GO
PRINT '1'
INSERT INTO dbo.Test VALUES('E', 41, '')
PRINT '2'
INSERT INTO dbo.Test VALUES('E', 41, 'a')
TRUNCATE TABLE dbo.Test
PRINT '3'
INSERT INTO dbo.Test VALUES('E', 41, 'a')
PRINT '4'
INSERT INTO dbo.Test VALUES('E', 41, 'b')
PRINT '5'
INSERT INTO dbo.Test VALUES('E', 41, 'c')
PRINT '6'
INSERT INTO dbo.Test VALUES('E', 41, '')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 11, 2014 at 3:52 pm
@Brandie: The Business Case for this (and others like it) are BOM lists to make sure you don't 'double tap' an item in the hierarchy. Doing it at the schema level just avoids future coding from avoiding your well tailored procedure, but I'm sure you knew that.
@scott: You missed a space in the first condition, near the end. Otherwise, that's a form of how I've done this in the past. However, we used NULL instead of ' '. Was more obvious and less prone to user errors.
@pdanes: My usual technique to deal with this is a trigger. When the inserted table comes in, the values are checked against existing entries in the table. The reason I usually do this via trigger instead of schema controls is so I can raise custom errors to the front end. The unfortunate part of doing it this way is you need to check both the rest of the inserted table for competing entries as well as the storage table, so it's the long way around. As long as you're not expecting heavy writes against the system though, you should be fine. Scott's way above is also valid as long as you can handle schema error codes in your front end where the users are entering items and translate them to users properly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply