SQL 2008r2 check constraint question 2

[wbodger]

I was not sure of this question would go here or Setup and Administration, but here goes. I need to put constraints on two columns in my db, but am a little unclear as to the specifics of achieving what I need. For one field I need to constrain the first character to a letter, then any letter or number or underscore, no spaces and all letters must be uppercase. the other is actually very close, just no uppercase constraint. And my installation is case insensitive.

Thanks!

wb

 

[gmmastros]

This should be pretty straight forward.

Alter Table YourTableName add constraint YourConstraintName check(YourColumnName1 Like '[A-Z][A-Z0-9_]%' Collate Latin1_General_BIN)

Alter Table YourTableName add constraint AnotherConstraintName check(YourColumnName2 Like '[A-Z][A-Z0-9_]%')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

So, using Collate Latin1_General_BIN constrains to all caps if using [A-Z] (and not [a-z})?

wb

 

[gmmastros]

correct

Take a look at these example:

Select 1
Where  'a123' Like '[A-Z][A-Z0-9_]%'

Select 2
Where  'a123' Like '[A-Z][A-Z0-9_]%' Collate Latin1_General_Bin

Select 3
Where  'a123' Like '[A-Za-z][A-Z0-9_]%' Collate Latin1_General_Bin

Note that when you run this in a query window, you will not get a "2" output because of the binary collation.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Great, thanks!

 

[wbodger]

I used these two commands to create my constraints

Alter Table dbo.tblCDE with nocheck
add constraint ckVariableName check (variablename Like '[A-Z][A-Z0-9_]%' Collate Latin1_General_BIN);
GO

Alter Table dbo.tblCDE with nocheck
add constraint chVariableNameShort check (variablenameshort Like '[A-Z][A-Z0-9_]%');
GO

And then tried updating variablename to violate the constraint and it let me. When I use the WITH NOCHECK option, does that mean it will never check the existing values even on update?

 

[1DMF]

The 'with nocheck' should be just for adding the constraint to not check the existing data, but it should check it once the constraint is added and an update is performed.

What data value did you use that you think should not be accepted?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[wbodger]

The old value was 'PPT_MED-HX_RADIATIONYN_CODE' (which actually does not meet the constraint, which was why I had to go with NOCHECK). Since this was for VARIABLENAME, I tried to simply replace 'CODE' with 'code' and it allowed it. However, if I try to replace the whole thing with 'code' it does not allow it, or if I try to start it with a leading 9 it does not allow it.

wb

 

[1DMF]

I believe it is to do with the case sensitive collation or lack of

http://stackoverflow.com/questions/1411161/sql-server-check-case-sensitivity

See if that helps.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[1DMF]

I believe it is to do with the case sensitive collation or lack of

http://stackoverflow.com/questions/1411161/sql-server-check-case-sensitivity

See if that helps.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[wbodger]

What is weird is this code

Collate Latin1_General_BIN

does make it case sensitive (the installation is case insensitive) and it works, at least on the first. I can even change 'PPT_MED-HX_RADIATIONYN_CODE' to 'ppt_MED-HX_RADIATIONYN_CODE' and the constraint catches the lowercase ppt, but anything after that first set of letters appears to no be checked by the constraint. For instance I changed it altogether from 'PPT_MED-HX_RADIATIONYN_CODE' to 'CODE_code', which should have failed because it was not all uppercase and it did not fail, but 'code' fails as it should. Further, 'CODEcode' works (it updates the field) and codeCODE fails (the constraint works, the update does not happen). I am now quite confused.

wb

 

[wbodger]

OK, I see the issue, just not sure how to get around it.

Alter Table dbo.tblCDE with nocheck
add constraint ckVariableName check (variablename Like '[A-Z][A-Z0-9_]%' Collate Latin1_General_BIN);
GO

only actually constrains the first two characters. What I am looking for is first character alpha and the rest alpha-numeric or underscore and then apply the case sensitivity check to ALL of that. Not sure why it took me so long to recognize that the % is really just anything works past the second space. I had been trying to find a way to use Regular Expressions, but it seems that Master Data Services is not available with the Standard Edition of SQL Server 2008r2, hence my search for another solution. Is there any way to say [A-Z0-9_] up to n times without having to actually repeat that string n times?

wb

 

[1DMF]

Not sure on the regex syntax in MS SQL but the collation you are using doesn't appear to have 'CS' in it, which stands for Case Sensitive ?

Looking here

http://technet.microsoft.com/en-us/library/ms179859.aspx

It seems [] only works for a single character, so

CODEcode will work because you are saying...

[A-Z][A-Z0-9_]%

First char upper A-Z , second char A-Z 0-9 or underscore then anything after that so in your example to me it seems it is matching

CO%

I'm not aware you can use full blown regular expressions in SQL so will the input have a fixed char length you can pattern match?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[wbodger]

It looks like perhaps

Alter Table dbo.tblCDE with nocheck
add constraint ckVariableName check (variablename Like '[A-Z]Replicate([A-Z0-9_],69)' Collate Latin1_General_BIN);
GO

Alter Table dbo.tblCDE with nocheck
add constraint chVariableNameShort check (variablenameshort Like '[A-Z]Replicate([A-Z0-9_],31)');
GO

may do what I want? My testing thus far says yes.

wb

 

[1DMF]

Typical, I was typing my post while you were obviously doing yours!

I found this, not sure if it helps :

http://msdn.microsoft.com/hi-in/magazine/cc163473(en-us).aspx

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[wbodger]

The thing I found was many people saying not to use UDFs inside of constraints as they do not often act as you are expecting. I think my solution using Replicate will actually work. I just went up to one less than the size of the field (since the first character has to be alpha) for the [A-Z0-9_] value.

wb

 

[gmmastros]

I apologize for giving you the wrong like pattern earlier. Before I advise you again, please take a look at this code.

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('CODE')
Insert Into @Temp Values('CODE123')
Insert Into @Temp Values('CODE-123')
Insert Into @Temp Values('CODE_123')
Insert Into @Temp Values('code')
Insert Into @Temp Values('Code')
Insert Into @Temp Values('1CODE')
Insert Into @Temp Values('C')
Insert Into @Temp Values('C1')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)


Select	Data, PatIndex('%[^A-Z0-9_]%', 
        Data COLLATE Latin1_General_BIN), 
        PatIndex('[^A-Z]%',  Data COLLATE Latin1_General_BIN)
From    @Temp

When you copy/paste this to a query window and run it, you should see 3 columns. THe first is just the data. The second and 3rd columns are the results of the PatIndex function.

I'm thinking that the correct test would be to make sure that the second and 3rd columns both return 0.

Anyway, please run the code. Add to the test data (if you want), and let me know what you think.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Oh yes, Replicate might do the job, good spot!

So it should be saying 1st char upper A-Z next 31 chars [A-Z0-9_]

http://technet.microsoft.com/en-us/library/ms174383.aspx

I've not seen it used in a WHERE/LIKE clause/operator before!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

Personally, I don't care for the "replicate" solution because you are hard coding the length of the column. If you change the length of the column in a year, will you remember to change the constraint also?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

George, I agree it is not ideal, but the issue with PatIndex that I see is the need to define the first character one way (alpha only) and the next n characters can be alpha numeric or underscore. I cannot figure out how to make PatIndex do this?

wb