Using: SQL Server 2000 SP3A Enterprise Edition
I have setup a table that holds application information. One of the fields
holds the Applications Version Information.
I have created a default type called Version of nvarchar and length 43.
Version information is made up of 2, 3 or 4 parts, Major, Minor, Build and
Revision (Major.Minor[.Build[.Revision]]). Each part can hold up to 10 digits
up to 2,147,483,647 (int without comas). That makes 4 blocks of 10 plus up t
o
3 seperators (being the .) makes 43 the max length.
What I want to do is create a rule that will only allow a valid version
number to be stored in the field. I had something like this:
@.value LIKE '[0-9].[0-9]' OR @.value LIKE '[0-9].[0-9].[0-9]' OR @.value LIKE
'[0-9].[0-9].[0-9].[0-9]'
This will not allow 1.10.8903.56 as [0-9] specifies single characters only.
Is there quick way to do the validation as a rule without having to type
loads of LIKE statements for every possibility?Use a CHECK constraint rather than a RULE. Rules and user-defined types
are supported for backwards compatibility. Constraints are more much
easier to maintain and code.
In this case I think you'll find it easier to exclude the values you
don't want:
CREATE TABLE YourTable
(... , version VARCHAR(43) NOT NULL
CHECK (version NOT LIKE '[^.0-9]'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Oops. That should be:
CREATE TABLE YourTable
(version NVARCHAR(43) NOT NULL
CHECK (version NOT LIKE '%[^.0-9]%'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Hi,
Thanks for quick response to my question!
All working okay now!
Just 1 other question! Why use a Check instead of a Rule? I was using the
rule on the default type to save me typing the Check for every field as I
have many tables that contain this Version type field. Your code works both
as a Check and Rule.
Cheers
Paul|||Yes it will work as a Check and a Rule. User-defined types, defaults
and rules are designated as backwards compatibility features so they
won't necessarily be fully supported in future versions of SQL Server.
Books Online recommends using the ANSI/ISO standard alternatives, CHECK
and DEFAULT constraints, instead.
User-defined types are difficult to maintain because of the convoluted
syntax and binding - you have to remove all references and unbind
before you can make a change - a big problem if your type is used in
many columns. Constraints are declarative, unbound and much more
flexible.
CHECK constraints can also be used by the optimizer (although that's
unlikely to be useful with the constraint used here). I don't think the
optimizer can take advantage of Rules, although I confess I don't
recall where I've seen that documented so someone may correct me on
that point.
Finally, I suspect fewer SQL Server professionals will continue to use
and remember the old syntax in future so those who inherit your code
will probably be more productive if they don't have to cope with the
legacy stuff.
I think those are enough reasons not to use User-defined Types and
Rules. You want to save yourself some typing? Just cut-and-Paste the
CHECK constraint in Query Analyzer - that's no more work than pasting
the name of a user-defined type.
David Portas
SQL Server MVP
--|||Hi,
Thanks again for your information, very useful.
I have updated to use Check instead of Rule, was just trying to do the easy
way but as you pointed out sometimes the easy way can become problamatic in
the future.
Cheers again for your help.
Paul|||If you use a datamodeling tool (I use ERwin) you probably can do much the
same thing in the model, but generating them out as CHECK constraints. They
have domains that you can use in the model but only generate them as CHECKS.
Not sure if other tools have this, but it is a really
feature of ERwin.----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:667A4E1E-64D2-4030-A25E-399B07E3C9B8@.microsoft.com...
> Hi,
> Thanks again for your information, very useful.
> I have updated to use Check instead of Rule, was just trying to do the
> easy
> way but as you pointed out sometimes the easy way can become problamatic
> in
> the future.
> Cheers again for your help.
>
> Paul
No comments:
Post a Comment