using check constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using check constraint

Author

Topic

archanasql
Starting Member

27 Posts

Posted - 2008-03-25 : 02:29:15

Hi,

I am creating a table in which i want a column to contain only these characters..ie

alphabets from a-z
alphabets from A-Z
numbers from 0-9
and allow characters
@
_ underscore..
.
- hyphen

ie suppose i have table called login..and i have 2 columns in this table named
Loginid LoginName...

So LoginName shuld accept only the above characters.while inserting data into this table..

Kindly help

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-25 : 02:51:02

ALTER TABLE dbo.Tab1
ADD CONSTRAINT chkRowCount CHECK (Col1 ='@' or Col1 ='_' or Col1 ='-' Col1 ='.' );

archanasql
Starting Member

27 Posts

Posted - 2008-03-25 : 03:01:56

Hi,

Thanks for your reply..
how shuld i add alphabets[a-z] and numbers[0-9] too in the same condition..

Moreover..the column should not accept special characters other than these 4 ..how shuld i do that.?

SusanthaB
Starting Member

14 Posts

Posted - 2008-03-25 : 06:17:20

http://www.sqlservercentral.com/articles/Administering/usingcheckconstraints/815/

Susantha Bathige
Senior DBA, Sri Lanka

judepieries
Starting Member

1 Post

Posted - 2008-03-25 : 06:57:14

Just a suggestion, why not consider to stop this at the UI level. It will definitely help in DB performance.

Your thoughts !!!!!

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-25 : 07:14:10

quote:
Originally posted by judepieries

Just a suggestion, why not consider to stop this at the UI level. It will definitely help in DB performance.

Your thoughts !!!!!

I agree to this. This looks to be a straight forward check which could be done using any front end tools.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-25 : 11:55:52

quote:
Originally posted by pravin14u

quote:
Originally posted by judepieries

Just a suggestion, why not consider to stop this at the UI level. It will definitely help in DB performance.

Your thoughts !!!!!
I agree to this. This looks to be a straight forward check which could be done using any front end tools.

I completely disagree with this line of thinking. There is a big difference between using a front end for formatting and data checking versus placing constraints on data. But, I am a bit of a data purist. :)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-25 : 18:42:36

Me too! If your data is important then you should enforce constraints at the database level. Also, most useful data is shared by many applications, not just one front end. You need to keep crap out of the database in these cases no matter what the source.

r.e. DB performance - a) Insignificant, b) Irrelevant compared to modelling the business rules. Get those wrong there is no point it getting there faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources