April 25, 2018 at 4:05 pm
Hi Dev Masters,
I had to call uncle on this one!
I am trying to build a check constraint on a table that tracks shared resources.
The shared resource percentage does not care how many components are included in the whole but does care that the components add up to 100 percent for a given effective date. I was trying use a natural key for the PK (see code below) but I ran into a scenario where on a specific date for the same entity, entity type, and attribute id I need to record factors that add up to 1 (or 100% ) depending on how you look at it.
Thanks for any help!
For example.
Entity = Food Plate
Entity type = Dinner
Effdate = 1/1/2016
entAttVal_int (FK to food component table) = Carrot
entAttVal_dbl (% of plate utilized by carrot) = 25%
Thanks a ton if you have any ideas!
/*=============================================================*/
IF (SELECT OBJECT_ID('EntAttValTb')) IS NOT NULL
DROP TABLE EntAttValTb;
go
--------
create table EntAttValTb
(
entId int
,entTypID int
,entAttId int
,effDate datetime
,entAttVal_int int
--,entAttVal_str varchar(255)
,entAttVal_dbl float
,constraint entAttVal_pk primary key(entId, entTypID, entAttId, effDate) -- this PK arrangement will backfire if more than one ratio needs to be documented for the same entity, entity type, entity attribute id
)
April 26, 2018 at 6:00 am
Have you thought this through? If you had such a CHECK constraint in place, how would you ever add anything new to the table? Assuming existing data already added up to 100%, you could only add rows that constituted 100%, because your CHECK constraint would prevent each row that didn't = 100% from being added. I'm pretty sure that is entirely impractical. The problem here is that you want a CHECK constraint to apply to a group of rows, and I'm pretty sure there's no way to do that. Anyone know otherwise, please edumacate me!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 26, 2018 at 6:31 am
Ever considered a trigger ( for insert / update / delete ) which checks your completeness and puts an extra column "TSAvailable' to current_timestamp, rolls back the insert/update if % > 100 and puts TSAvailable to 2999-01-01 if % < 100.
Just my 2 ct
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 26, 2018 at 7:07 am
sgmunson - Thursday, April 26, 2018 6:00 AMHave you thought this through? If you had such a CHECK constraint in place, how would you ever add anything new to the table? Assuming existing data already added up to 100%, you could only add rows that constituted 100%, because your CHECK constraint would prevent each row that didn't = 100% from being added. I'm pretty sure that is entirely impractical. The problem here is that you want a CHECK constraint to apply to a group of rows, and I'm pretty sure there's no way to do that. Anyone know otherwise, please edumacate me!
Thinking this through has been tough! You are correct in stating the problem here is that I want a CHECK constraint to apply to a group of rows. I was almost thinking do it at the application layer but wanted to check with the gurus before bastardizing my model. Thanks for looking into this with me, hope I can help you one day too!.
April 26, 2018 at 7:12 am
ALZDBA - Thursday, April 26, 2018 6:31 AMEver considered a trigger ( for insert / update / delete ) which checks your completeness and puts an extra column "TSAvailable' to current_timestamp, rolls back the insert/update if % > 100 and puts TSAvailable to 2999-01-01 if % < 100.Just my 2 ct
For some reason the database training I was brainwashed with since 2011 told me to avoid triggers like the plague. I am not opposed to using one I just never have before.
What would TSAvailable show when the group reaches 100% ?
Thanks for looking into this with me, hope I can help you one day too!
April 26, 2018 at 7:23 am
sgmunson - Thursday, April 26, 2018 6:00 AMHave you thought this through? If you had such a CHECK constraint in place, how would you ever add anything new to the table? Assuming existing data already added up to 100%, you could only add rows that constituted 100%, because your CHECK constraint would prevent each row that didn't = 100% from being added. I'm pretty sure that is entirely impractical. The problem here is that you want a CHECK constraint to apply to a group of rows, and I'm pretty sure there's no way to do that. Anyone know otherwise, please edumacate me!
Nope, you're right on that Steve. When you insert or update into a table, the check constraint is evaluated at row level, not at the dataset + table level. Although you could create a CONSTRAINT that works like the OP asked, it would completely prevent the addition of any rows apart from a singular 1. See the below example:
CREATE TABLE SampleTable (ID int,
DecValue decimal(3,2));
GO
CREATE FUNCTION IDTotalValue (@ID int)
RETURNS decimal(3,2)
AS BEGIN
DECLARE @DecValueTotal decimal(3,2);
SELECT @DecValueTotal = SUM(DecValue)
FROM SampleTable
WHERE ID = @ID;
RETURN @DecValueTotal;
END
GO
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (dbo.IDTotalValue(ID) = 1.00);
GO
INSERT INTO SampleTable
VALUES (1,1),
(2, 0.7),
(2, 0.3),
(3, 0.2),
(3, 0.55),
(3, 0.25);
GO
GO
DROP TABLE SampleTable;
DROP FUNCTION IDTotalValue;
Notice that the INSERT fails, even though the total for all 3 IDs comes to 1.
So, like Johan suggested, the best method would be to use a TRIGGER instead. It's still going to be problematic, as if you have many rows to update you have to do them in the same batch, however, this should put you on the right step:
CREATE TABLE SampleTable (IdentityID int IDENTITY(1,1),
ID int,
DecValue decimal(3,2));
GO
CREATE TRIGGER DecValueTotal ON SampleTable AFTER INSERT, UPDATE, DELETE
AS
IF EXISTS (SELECT COUNT(*) FROM SampleTable GROUP BY ID HAVING SUM(DecValue) != 1) BEGIN
RAISERROR(N'The sum of the values of DecValue must be 1 for each ID.', 11,1);
ROLLBACK;
END
GO
INSERT INTO SampleTable
VALUES (1,1),
(2, 0.7),
(2, 0.3),
(3, 0.2),
(3, 0.55),
(3, 0.25);
GO
--This will fail
UPDATE SampleTable
SET DecValue = 0.7
WHERE ID = 1;
GO
SELECT *
FROM SampleTable;
GO
--This will work
UPDATE SampleTable
SET DecValue = 0.5
WHERE ID = 2;
GO
SELECT *
FROM SampleTable;
GO
--These will fail, as they are separate statements.
--After the first UPDATE, the sum of DecValue will be 0.95, not 1
UPDATE SampleTable
SET DecValue = 0.5
WHERE IdentityID = 5;
UPDATE SampleTable
SET DecValue = 0.3
WHERE IdentityID = 6;
GO
SELECT *
FROM SampleTable;
GO
--This will work, as the trigger will occur after both rows are updated, which will result in the sum of DecValue being 1
UPDATE SampleTable
SET DecValue = CASE IdentityID WHEN 5 THEN 0.5
WHEN 6 THEN 0.3 END
WHERE IdentityID IN (5,6);
GO
SELECT *
FROM SampleTable;
GO
DROP TABLE SampleTable;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2018 at 9:07 am
Thom A - Thursday, April 26, 2018 7:23 AMsgmunson - Thursday, April 26, 2018 6:00 AMHave you thought this through? If you had such a CHECK constraint in place, how would you ever add anything new to the table? Assuming existing data already added up to 100%, you could only add rows that constituted 100%, because your CHECK constraint would prevent each row that didn't = 100% from being added. I'm pretty sure that is entirely impractical. The problem here is that you want a CHECK constraint to apply to a group of rows, and I'm pretty sure there's no way to do that. Anyone know otherwise, please edumacate me!Nope, you're right on that Steve. When you insert or update into a table, the check constraint is evaluated at row level, not at the dataset + table level. Although you could create a CONSTRAINT that works like the OP asked, it would completely prevent the addition of any rows apart from a singular 1. See the below example:
CREATE TABLE SampleTable (ID int,
DecValue decimal(3,2));
GOCREATE FUNCTION IDTotalValue (@ID int)
RETURNS decimal(3,2)
AS BEGIN
DECLARE @DecValueTotal decimal(3,2);SELECT @DecValueTotal = SUM(DecValue)
FROM SampleTable
WHERE ID = @ID;RETURN @DecValueTotal;
END
GOALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (dbo.IDTotalValue(ID) = 1.00);
GOINSERT INTO SampleTable
VALUES (1,1),
(2, 0.7),
(2, 0.3),
(3, 0.2),
(3, 0.55),
(3, 0.25);
GOGO
DROP TABLE SampleTable;
DROP FUNCTION IDTotalValue;
Notice that the INSERT fails, even though the total for all 3 IDs comes to 1.So, like Johan suggested, the best method would be to use a TRIGGER instead. It's still going to be problematic, as if you have many rows to update you have to do them in the same batch, however, this should put you on the right step:
CREATE TABLE SampleTable (IdentityID int IDENTITY(1,1),
ID int,
DecValue decimal(3,2));
GOCREATE TRIGGER DecValueTotal ON SampleTable AFTER INSERT, UPDATE, DELETE
ASIF EXISTS (SELECT COUNT(*) FROM SampleTable GROUP BY ID HAVING SUM(DecValue) != 1) BEGIN
RAISERROR(N'The sum of the values of DecValue must be 1 for each ID.', 11,1);
ROLLBACK;
END
GOINSERT INTO SampleTable
VALUES (1,1),
(2, 0.7),
(2, 0.3),
(3, 0.2),
(3, 0.55),
(3, 0.25);GO
--This will fail
UPDATE SampleTable
SET DecValue = 0.7
WHERE ID = 1;
GO
SELECT *
FROM SampleTable;
GO
--This will work
UPDATE SampleTable
SET DecValue = 0.5
WHERE ID = 2;
GO
SELECT *
FROM SampleTable;
GO
--These will fail, as they are separate statements.
--After the first UPDATE, the sum of DecValue will be 0.95, not 1
UPDATE SampleTable
SET DecValue = 0.5
WHERE IdentityID = 5;
UPDATE SampleTable
SET DecValue = 0.3
WHERE IdentityID = 6;
GO
SELECT *
FROM SampleTable;
GO
--This will work, as the trigger will occur after both rows are updated, which will result in the sum of DecValue being 1
UPDATE SampleTable
SET DecValue = CASE IdentityID WHEN 5 THEN 0.5
WHEN 6 THEN 0.3 END
WHERE IdentityID IN (5,6);
GO
SELECT *
FROM SampleTable;
GO
DROP TABLE SampleTable;
A good step in the right direction, but you could have easily made that function an inline table-valued one. Also, as good as this is, it's still impractical, because you'd ALWAYS have to do record inserts or updates in batches that add up to 100%. No longer could you edit one row and then edit another one to bring it back into compliance. That could turn out to be far more inconvenient than it's worth, but that is a decision the original poster will have to make.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 26, 2018 at 9:14 am
sgmunson - Thursday, April 26, 2018 9:07 AMA good step in the right direction, but you could have easily made that function an inline table-valued one. Also, as good as this is, it's still impractical, because you'd ALWAYS have to do record inserts or updates in batches that add up to 100%. No longer could you edit one row and then edit another one to bring it back into compliance. That could turn out to be far more inconvenient than it's worth, but that is a decision the original poster will have to make.
I'm pretty sure you can't reference an inline-table function in a check constraint; hence why the use of a Scalar function. Otherwise, yes I would have use an inline-table function. For example: CREATE FUNCTION IDTotalValue (@ID int) RETURNS TABLE AS
RETURN
SELECT SUM(DecValue) AS TotalDecValue
FROM SampleTable
WHERE ID = @ID
GO
If you try: ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (dbo.IDTotalValue(ID).TotalDecValue = 1.00)
You'll get the error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.IDTotalValue", or the name is ambiguous.
If you try :ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (SELECT TotalDecValue FROM dbo.IDTotalValue(ID) = 1.00);
You'll get the error: Incorrect syntax near the keyword 'SELECT'.
And yes, I made that caveat about the problems a trigger will have still, and why I gave examples of such behaviour in the sample 🙂 :
It's still going to be problematic, as if you have many rows to update you have to do them in the same batch
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2018 at 11:10 am
You could make the restriction 100% or less. The main thing is not overfill it, right? That would allow changes, you would just have to remove/reduce something before adding something else. You could have a log / periodic edit that would alter you if a plate stays below 100% for too long.
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".
April 26, 2018 at 8:34 pm
Another option is to make use of an indexed view/materialized view to implement the logic(sum of all percentage to be > 100%) and have a unique clustered index on it.
So if the rule did NOT violate the <=100% rule the indexed view would return nothing and if it did the unique clustered index would throw the error.
Check the link which shows the details of doing this along with the caveats
https://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/
I haven't tested this myself so not sure if this is going to get called per row insert basis in which case it is not going to help.
April 27, 2018 at 10:23 am
Thom A - Thursday, April 26, 2018 9:14 AMsgmunson - Thursday, April 26, 2018 9:07 AMA good step in the right direction, but you could have easily made that function an inline table-valued one. Also, as good as this is, it's still impractical, because you'd ALWAYS have to do record inserts or updates in batches that add up to 100%. No longer could you edit one row and then edit another one to bring it back into compliance. That could turn out to be far more inconvenient than it's worth, but that is a decision the original poster will have to make.I'm pretty sure you can't reference an inline-table function in a check constraint; hence why the use of a Scalar function. Otherwise, yes I would have use an inline-table function. For example:
CREATE FUNCTION IDTotalValue (@ID int) RETURNS TABLE AS
RETURN
SELECT SUM(DecValue) AS TotalDecValue
FROM SampleTable
WHERE ID = @ID
GOIf you try:
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (dbo.IDTotalValue(ID).TotalDecValue = 1.00)
You'll get the error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.IDTotalValue", or the name is ambiguous.If you try :
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (SELECT TotalDecValue FROM dbo.IDTotalValue(ID) = 1.00);
You'll get the error: Incorrect syntax near the keyword 'SELECT'.And yes, I made that caveat about the problems a trigger will have still, and why I gave examples of such behaviour in the sample 🙂 :
It's still going to be problematic, as if you have many rows to update you have to do them in the same batch
Hmmm... just looks like the following code:
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (SELECT TotalDecValue FROM dbo.IDTotalValue(ID) = 1.00);
Should be written just a tad differently:
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK ((SELECT TotalDecValue FROM dbo.IDTotalValue(ID)) = 1.00);
And if that fails too, oh well. In any case, I've learned something new about CHECK constraints.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 30, 2018 at 2:03 am
sgmunson - Friday, April 27, 2018 10:23 AMHmmm... just looks like the following code:
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (SELECT TotalDecValue FROM dbo.IDTotalValue(ID) = 1.00);
Should be written just a tad differently:
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK ((SELECT TotalDecValue FROM dbo.IDTotalValue(ID)) = 1.00);And if that fails too, oh well. In any case, I've learned something new about CHECK constraints.
The second one returns:
Msg 1046, Level 15, State 1, Line 15
Subqueries are not allowed in this context. Only scalar expressions are allowed.
You had me questioning my sanity there Steve!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2018 at 8:12 am
Thom A - Monday, April 30, 2018 2:03 AMsgmunson - Friday, April 27, 2018 10:23 AMHmmm... just looks like the following code:
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK (SELECT TotalDecValue FROM dbo.IDTotalValue(ID) = 1.00);
Should be written just a tad differently:
ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK ((SELECT TotalDecValue FROM dbo.IDTotalValue(ID)) = 1.00);And if that fails too, oh well. In any case, I've learned something new about CHECK constraints.
The second one returns:
Msg 1046, Level 15, State 1, Line 15
Subqueries are not allowed in this context. Only scalar expressions are allowed.You had me questioning my sanity there Steve!
Okay, it interpreted it as a subquery, when it needs a scalar expression. Perhaps adding TOP (1) ?ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK ((SELECT TOP (1) TotalDecValue FROM dbo.IDTotalValue(ID)) = 1.00);
That may still fail, but I have to at least be thorough enough to TRY to meet it's needs... that's just me being me...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 30, 2018 at 8:49 am
sgmunson - Monday, April 30, 2018 8:12 AMOkay, it interpreted it as a subquery, when it needs a scalar expression. Perhaps adding TOP (1) ?ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK ((SELECT TOP (1) TotalDecValue FROM dbo.IDTotalValue(ID)) = 1.00);That may still fail, but I have to at least be thorough enough to TRY to meet it's needs... that's just me being me...
Using TOP 1 doesn't cause a sub query stop being a sub query. I gave it a go for completeness though, and the same error. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2018 at 9:33 am
Thom A - Monday, April 30, 2018 8:49 AMsgmunson - Monday, April 30, 2018 8:12 AMOkay, it interpreted it as a subquery, when it needs a scalar expression. Perhaps adding TOP (1) ?ALTER TABLE SampleTable ADD CONSTRAINT DecValueTotal_CK CHECK ((SELECT TOP (1) TotalDecValue FROM dbo.IDTotalValue(ID)) = 1.00);That may still fail, but I have to at least be thorough enough to TRY to meet it's needs... that's just me being me...
Using TOP 1 doesn't cause a sub query stop being a sub query. I gave it a go for completeness though, and the same error. 🙂
Then that's the answer. A thorough test proves it will not work. Try not to question your sanity, though... I'm pretty sure you're fine...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply