Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    Red face Unanswered: CHECK Constraint question (easy)

    I want to add a check constraint to this table I'm working with... well, a few constraints, actually.

    I generally want to check the length of string values. I'm... pretty n00bish at writing T-SQL and I strictly speaking know how to write a constraint, I just... don't know how to do it in practice yet.

    A little...tiny bit of help... Please, thank you.

    I think it ought to look something like this:

    Code:
    ALTER TABLE tblUsers
    ADD CONSTRAINT ckLength
    CHECK (Len(Username) < 6)
    Does that look right, or... am I just thinking too hard here?
    Last edited by asherman86; 12-07-11 at 17:57.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you just define the column as VARCHAR(5), this becomes a non-issue.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32
    That isn't the point though! I'm trying to learn how to write constraints to check for this or that, or limit something.

    It's a process.

    But while you're in the mood to give me some answers, how would you write a constraint to parse a password string and check it for a combination of numbers and letters?

    also. I know that I could probably very easily handle that mess in the front end of my application, but front-end validation isn't always 100% reliable. I'm just trying to get better at doing this stuff in the database end.


  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Gnaw on this one for a bit, it should entertain you:
    Code:
    CREATE TABLE asherman86 (
       pwd          NVARCHAR(999)
       CONSTRAINT XCKasherman86
          CHECK (pwd COLLATE SQL_Latin1_BIN LIKE N'%[A-Z]%'
             AND pwd COLLATE SQL_Latin1_BIN LIKE N'%[a-z]%'
             AND pwd LIKE N'%[0-9]%')
       )
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32
    Quote Originally Posted by Pat Phelan View Post
    Gnaw on this one for a bit, it should entertain you:
    Code:
    CREATE TABLE asherman86 (
       pwd          NVARCHAR(999)
       CONSTRAINT XCKasherman86
          CHECK (pwd COLLATE SQL_Latin1_BIN LIKE N'%[A-Z]%'
             AND pwd COLLATE SQL_Latin1_BIN LIKE N'%[a-z]%'
             AND pwd LIKE N'%[0-9]%')
       )
    -PatP
    :: chew chew chew chew chew :: that's a lot of stuff to chew on! @_@

    ...I figured something out though!

    I wrote a little function ^_^

    Code:
    ALTER FUNCTION [dbo].[hasNumerics](@pInput varchar(max))
    RETURNS BIT
    	BEGIN
    		DECLARE @vInputLength INT
    		DECLARE @vIndex INT
    		DECLARE @vCount BIT
    		DECLARE @SearchChar CHAR(1)
    		
    		SET @vCount = 0
    		SET @vIndex = 0
    		SET @vInputLength = LEN(@pInput)
    		
    		WHILE (@vIndex <= @vInputLength AND @vCount = 0)
    			BEGIN
    				SET @SearchChar = SUBSTRING(@pInput, @vIndex, 1)
    				
    				IF (ISNUMERIC(@SearchChar)=1)
    					SET @vCount = @vCount + 1
    				SET @vIndex = @vIndex + 1
    			END
    			
    		RETURN @vCount
    		
    	END
    ...and another Constraint!

    Code:
    ALTER TABLE tblUsers WITH NOCHECK 
    ADD CONSTRAINT chkNumerics
    CHECK ( dbo.hasNumerics(UPassword) <> 0)
    ...and when it worked, I felt really really cool!


  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummm... Yeah. That would do what line 6 of my example does.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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