rules on a table

In My table i'm collecting information about our customers.In that i have fields zip and phone.So i wanted to implemement a rule that zip should be atlaest of of 5 characters and phone should be 13 characters Including '-'s(333-333-3333).How can i implement these two rules on my table.First of all, decide if you really want to add those constraints. They will automagically limit your database to dealing with data like what you'd find in the United States.

If that is an Ok thing, then I would suggest that you add constraints to your table. You could use something like:ALTER TABLE myTable
ADD CONSTRAINT XCK01myTable CHECK (5 <= Len(zip))
, ADD CONSTRAINT XCK02myTable CHECK (phone
LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')Constraints like these make me nervous, since I see them as rather arbitrary, but they are much better done as constraints than as code because you can easily change them in the database if needed, instead of having to hunt down hundreds or thousands of snippets of code!

-patP|||I'm with Pat. I think you'll end up regretting this as the users start to complain about limiting their ability to enter data. The zip-code you might get away with, though what about zip-4 extensions? I don't think enforcing a 13 character phone number is going to make you a lot of friends with the users.|||True, but you can fix this in one place (the database) with one command (DROP CONSTRAINT) if it turns out to be a problem. This is far better than coding it into an executable or a web page, at least in my opinion.

-PatP|||Oh, I agree. Table constraints are the best places to store design flaws, hands down. :p|||I don't know that it's a design flaw to insure data consistency in the data layer, even if that means adding "another" layer to the data. It's isn't so much to cover design flaws as it is to allow the data group to insure data is consistent and secure for the enterprise.|||It's not a design flaw to enforce data integrity. I'm just concerned about enforcing this particular constraint, based on past experience. I guess I'd say it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users. Unless there is some sort of application process that depends upon the phone number being absolutely 13 characters, then why place an artificial limit on how the database can be used? You are locking out the possibility of international numbers, extensions, etc...|||i agree with blindman

"it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users"

what about the guy who paid a few thousand bucks to secure the number 1-800-BEST-DBA

this guy's going to be p1ssed if you force him to put the dash where you want him to put the dash

note for those who still don't get it: there is no dash on the phone keypad|||That's not an unnecessary constraint. 1800BESTDBA isn't a phone number. It's a way to represent a phone number. You can't dial it though. You can't feed it to a dialer or use it for customer service. Anyone looking at the number is going to wonder what the idiot was thinking putting it into the database like that anyway. If you want to have a descriptor for the phone number, then have one. He can put whatever he wants in there. 1-800-BEST-DBA, 1-800-STUPID-Q...whatever.

The number is 18003334444. The display handled on the front-end is 1-800-333-4444. The descriptiong, which can be displayed or not is 1-800-STUPID-Q.|||well, that just proves my point

the number is not 18003334444

it's 18002378322 -- you could look it up!

and yes, i sure can dial 1-800-BEST-DBA

even on my rotary phone!!

and of course your very descriptive "what the idiot was thinking..." reveals an attitude that might best be set aside when dealing with people in the real world