validation rule/text & input mask

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

validation rule/text & input mask

Author

Topic

rosy22
Starting Member

4 Posts

Posted - 2010-12-02 : 07:29:18

Hi,

I am new to SQL and i wanted to know if anyone can help me answer a couple of questions.
I need to show SQL statements of how i created my tables which i created using MS Access. I have used validation rules and text to restrict user input as well as input masks to restrict user formatting.
For example i have a field called title which should only accept the values "MR or MRS or MISS or MRS" from a drop down list and the validation text is "You must select an option from the list". How would i define this in a SQL statement would it be something like this:
CONSTRAINT title
CHECK (title LIKE MR or MRS or MISS or MRS'. Is this the correct way to define a alidation rule in SQL and how would i define the validation text?
Also for the input mask i have set for the field forename '>L<L??????????????????' which ensures that the 1st letter is capital and followed by lower case. What is the SQL statement for input masks?

My final question is in regards to multiple foreign keys in the same table. I have 5 foreign keys in one table how will i define this in a SQL statement.

Thanks for your help in advance, its much appreciated :)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 07:57:51

check constraint

ALTER TABLE tableName	
ADD CONSTRAINT ck_title CHECK (title in ('MR', 'MRS', 'MISS'))

But really, better to populate a table with the valid values and use a foreign key.

Foreign Key


ALTER TABLE tableName
ADD CONSTRAINT constraintName Foreign Key (columnName)
	References otherTable (columnName);

rosy22
Starting Member

4 Posts

Posted - 2010-12-02 : 08:13:04

Thanks for the reply, shall i do the alter table right at the end of the entire SQL statement or beneath each field that has the validation rule? What about the input mask and validation text how will i show that in sql statement?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 08:46:15

you can create the constraint(s) after the CREATE TABLE statement, or inline like so

CREATE TABLE tableName (
	name varchar(50),
	title varchar(4),
	Constraint ck_title CHECK (title in ('MRS', 'MR', 'MISS')),
	Constraint ck_name_sentenceCase CHECK (
		convert(varbinary, left(name, 1)) = convert(varbinary, UPPER(left(name, 1)))
	)
);
GO

The second check constraint will make sure the 1st character is upper case.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 08:49:04

By the way, upper/lower case, I'll leave to the presentation layer, and not enforce in the database -- unless there is a very compelling reason.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources