"validation rule" on a rowable?

  • 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!

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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).

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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'

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I updated my last post, please look above...

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • Thank You (both) very much. I'm so stupid ;)))

  • 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