Putting a constraint on a field in SQL 2005

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

SQL Server 2005 Forums

Transact-SQL (2005)

Putting a constraint on a field in SQL 2005

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-03-12 : 22:15:39

I'm a C# developer, been doing database stuff for a long time. Really though, I have never added any constraints to my table fields. I was asked to put a constraint on an email field the other day to make sure it's required.

To me, a constraint has been to uncheck the allow null box when creating a new table.

I know of constraints and that you can create them aside from making sure a field is not null.

And I've disabled constraints on tables others have created.

So I don't understand though if I'm to put a constraint on a specific field, how, and what is that going to do when my C# DL code doesn't insert an email. Isn't just ensure it's not null check on the DB field good enough when designing the table? There isn't going to be any tables related to this so I don't see why I need an actual constraint...but my boss wanted one to make sure email is required at the table level.

I just don't know what to expect (effect) of doing this with my C# code or otherwise and best way to add this.

I'm not a DBA but have done quite a bit of work in design. But just not heavy on creating constraints so I need to understand them more in context to my code as well as just SQL itself.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 01:05:25

Okay think of of this way you set email field to NOT NULL .. not null does not mean that it is an email address; what if they enter " " ? That is not null .. so it will succeed. You can add a check constraint on the field to make sure it is NON-EMPTY string of characters that match email format.

I know you can check for that kind of stuff in C# code but lets say you do a batch import? No way for you C# code to come check the batch; if you have it in DB it catches it before it becomes an issue.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-03-13 : 14:34:04

ok so in the check contstraints expression box (I'm adding this through the UI, not code) I'd add NON-EMPTY for the expression?

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 19:46:40

len(Ltrim(Rtrim(Field))) > 0

thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources