CHECK Constraint question (easy)

asherman86 · #1 (**permalink**) 12-07-11, 16:43

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?

Pat Phelan · #2 (**permalink**) 12-07-11, 16:58

If you just define the column as VARCHAR(5), this becomes a non-issue.

-PatP

asherman86 · #3 (**permalink**) 12-07-11, 17:14

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.

Pat Phelan · #4 (**permalink**) 12-07-11, 18:03

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

asherman86 · #5 (**permalink**) 12-07-11, 18:20

Quote:

Originally Posted by Pat Phelan

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!

Pat Phelan · #6 (**permalink**) 12-08-11, 09:21

Ummm... Yeah. That would do what line 6 of my example does.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech