April 19, 2015 at 5:01 pm
Hello,
I'm sorry for error in the topic title, intentional is:
"validation rule" on a row of table?
I'm looking for validation data in the table - when inserting or updating the row. I mean some rule checking consistency of columns data in the row (for example: if in the same row in 1st column will have value 55 and in 2nd column will have value 1, it must deny this insert/update of row). Is this possible in MS SQL at all?
Thanks for suggests!
April 19, 2015 at 6:36 pm
domingo.sqlservercentral (4/19/2015)
Hello,I'm sorry for error in the topic title, intentional is:
"validation rule" on a row of table?
I'm looking for validation data in the table - when inserting or updating the row. I mean some rule checking consistency of columns data in the row (for example: if in the same row in 1st column will have value 55 and in 2nd column will have value 1, it must deny this insert/update of row). Is this possible in MS SQL at all?
Thanks for suggests!
I think you want to look into CHECK CONSTRAINTs.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 20, 2015 at 3:51 pm
I found examples for many columns, as below:
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
but... it works independly for these columns, as two simple check constraints. I need a rule for relationship beetween few columns values. And refusing only when inserting/updating row with SOME 'a' value in 1st column and SOME 'b' value in the 2nd (only when both conditions are true).
April 20, 2015 at 5:36 pm
domingo.sqlservercentral (4/20/2015)
I found examples for many columns, as below:CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
but... it works independly for these columns, as two simple check constraints. I need a rule for relationship beetween few columns values. And refusing only when inserting/updating row with SOME 'a' value in 1st column and SOME 'b' value in the 2nd (only when both conditions are true).
Your best bet would probably be to post DDL for your example table, along with some INSERT or UPDATE statements that should both be allowed and be blocked. With a clear explanation of why each should be allowed or blocked.
This approach will be the fastest way to get a tested, working solution to your situation.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 21, 2015 at 5:04 am
My tested example was:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id<>0 AND City<>'Sandnes')
)
GO
INSERT INTO Persons
VALUES (1, 'MyLastname', 'MyFirstname', 'MyAddress', 'London')
GO
--
But I need to DENY insert/update row only when its P_Id will be 0 and simultanously City will be 'Sandnes', not independly - as with example update:
UPDATE Persons
SET P_Id=0, City='Sandnes'
April 21, 2015 at 5:11 am
domingo.sqlservercentral (4/20/2015)
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')but... it works independly for these columns, as two simple check constraints.
That's one check constraint that checks both columns, it's not two independent ones. Check constraints are exactly what you're asking for, rules enforcement among columns of a table. Express your requirement as a logical condition and use that in a check constraint
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2015 at 5:15 am
I updated my last post, please look above...
April 21, 2015 at 5:20 am
domingo.sqlservercentral (4/21/2015)
But I need to DENY insert/update row only when its P_Id will be 0 and simultanously City will be 'Sandnes', not independly - as with example update:UPDATE Persons
SET P_Id=0, City='Sandnes'
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (NOT (P_Id=0 AND City='Sandnes'))
)
GO
INSERT INTO Persons
VALUES (1, 'MyLastname', 'MyFirstname', 'MyAddress', 'London')
GO -- succeeds
UPDATE Persons
SET P_Id=0, City='Sandnes'
Msg 547, Level 16, State 0, Line 16
The UPDATE statement conflicted with the CHECK constraint "chk_Person". The conflict occurred in database "Test", table "dbo.Persons".
As I said above, if you have one check constraint with multiple conditions, the conditions are checked simultaneously, they are not independent. Multiple check constraints with single conditions are checked independently.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2015 at 5:21 am
OK, so for the operation to be allowed, you need City to be other than Sandnes, or you need P_ID to be other than 0. So just change the AND in your constraint to OR.
John
April 21, 2015 at 5:23 am
Thank You (both) very much. I'm so stupid ;)))
April 27, 2015 at 7:58 am
Thank you!
This syntax works for me too. And I mean it will be usefull for me in few other cases 🙂
Kind regards.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply