dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: Check Constraint Help

  1. #1
    Join Date
    Oct 2019
    Location
    Ahmedabad
    Posts
    5

    Check Constraint Help

    Can i create Custom check constraint in SQL Server?

    I have a table 'City' having columns as 'ID' , 'Name' and 'Number'.
    If Name is 'AA' then Number column shouldn't accept null values. If name is other than 'AA' Number column can be null

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,146

    Re: Check Constraint Help

    I believe a constraint can call a function, but I'm on my phone so I really can't look into it fully. However, doing a google search for, "sql constraint calls function" seemed to look promising. Hopefully, this will help.

  3. #3
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,543

    Re: Check Constraint Help

    Quote Originally Posted by Sherin Mathew View Post
    Can i create Custom check constraint in SQL Server?

    I have a table 'City' having columns as 'ID' , 'Name' and 'Number'.
    If Name is 'AA' then Number column shouldn't accept null values. If name is other than 'AA' Number column can be null
    You could implement an "Instead ff" Trigger like:
    Code:
    CREATE TRIGGER dbo.OnInsertCity 
       ON  dbo.City 
       INSTEAD OF INSERT
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        DECLARE @city varchar(50),@number int
        SELECT @city=[name] ,@number=[number] from inserted
        IF (@number IS NULL and @city ='AA')
        BEGIN
            RAISERROR ('You are not allowed to Add These Data.', 10, 11)
        END
        ELSE
            INSERT INTO City ([name] ,[number]) values (@city,@number)
    
    END
    GO
    Victor Nijegorodov

  4. #4
    Join Date
    Oct 2019
    Posts
    3

    Re: Check Constraint Help

    Hello,
    You can call a UDF in check constraint which will work as per your requirement. Hope this will work as per your requirement.

  5. #5
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,543

    Re: Check Constraint Help

    Quote Originally Posted by ishan_shah View Post
    Hello,
    You can call a UDF in check constraint which will work as per your requirement. Hope this will work as per your requirement.
    Could you post an example for the OP's case
    Victor Nijegorodov

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)