Domains (Data Type) - Set theory validation / constraint 1

[1DMF]

Hi,

How do I create domains in MS SQL for data typing?

I wish to set up data typing / validation via Domain, and I can't seem to find how.

Googling throws up results that imply this isn't possible and all you can do is implement a check constraint, which sounds bizarre.

I certainly wouldn't want to have to create a CK in every table that uses a certain 'Domain', let alone the nightmare of maintaining a plethora of CK's across multiple tables.

I must be missing something obvious somewhere.

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

Take a look here.

http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/07/426930.aspx

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George, thought I must be missing something simple somewhere.

So it's UDT's via DB->Types->User-Defined Data Types

Much obliged.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

I seem to be stumped.

I don't understand how I add the actual constraint (rule). There is a section to select a rule to apply to the data type, but no way of defining the rule?

Right click on the 'Rules' offers me 'Powershell', 'Reports', 'Refresh'.

Where do I put

@VALUE IN ('a','b','c')

?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

Well after a lot of faffing around, it seems MS's implementation of 'Domains' is diabolically bad including the GUI.

There is no way I can find whatsoever to create a 'Rule' while creating a UDT. There isn't even a GUI to create the 'Rule' separately either!

You have to use DDL (unbelievable for a GUI OS!)

http://technet.microsoft.com/en-us/library/aa258252(v=sql.80).aspx

Then MS's info on syntax is bizarre, the link above shows...

CREATE RULE range_rule
AS
@range >= $1000 AND @range < $20000


B. Rule with a list

This example creates a rule that restricts the actual values entered into the column or columns (to which this rule is bound) to only those listed in the rule.
CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877')


C. Rule with a pattern

This example creates a rule to follow a pattern of any two characters followed by a hyphen, any number of characters (or no characters), and ending with an integer from 0 through 9.
CREATE RULE pattern_rule
AS
@value LIKE '_ _-%[0-9]'

So surely the last one should be

CREATE RULE pattern_rule 
AS
[b]@pattern[/b] LIKE '_ _-%[0-9]'

In the example all the variable names for the input argument value seem to be the word in front of the underscore except this one?

I used @value and that seems to work?

So do you just make it up as you go along, calling the @variable what ever you feel like and not even declaring it?

University taught me when talking to domain or check constraint arguments there is an in-built variable called @VALUE which is what you apply the validation against.

I don't understand where the variables have come from in the tech net example, what is the correct variable name to use?







"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

I don't use UDT's or rules. The link I found seemed promising. Unfortunately, I looked a little more and found this:

http://technet.microsoft.com/en-us/library/ms188064.aspx

In that link:

{quote]This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE.[/quote]

My guess is that it will take a really long time for Microsoft to actually remove this functionality, but that is my own guess with nothing concrete to base it on. I think it will be around for a while longer yet because it would prevent people from upgrading to a newer version of SQL Server.

Regardless... since you are not currently using this functionality, I cannot recommend that you start. I apologize.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Wow - thanks George, as I said the implementation especially through the Studio Management GUI was not user friendly to say the least. Perhaps this is partly why.

I can see the flexibility with a rule being attached, but it is a poor model of the 'Domain' concept, or at least compared to what I was taught and currently use with PostGreSQL.

Though using check constraints defeat the point of 'Domains' as well as DRYness not to mention maintenance headache should multiple fields across multiple tables using the same constraint need altering.

This also means as CK's are applied at table level, it requires duplication of the CK code and each CK must have a different name even though they are for the same purpose. It gets ugly trying to implement 'Domain' data typing logic via table level check constraints.

Regardless... since you are not currently using this functionality, I cannot recommend that you start. I apologize.

Well I wasn't until @ 4pm today! - No worries , it's all a C.P.D. to chalk up to experience!

However, do you have any other suggestion to achieve my 'domains' goal in MS SQL ?

1DMF.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

Well it would seem with MS SQL, your options are limited.

1. Domains don't exist
2. UDT's / Rules are being deprecated
3. CK's can't use sub-queries

I've decided to implement a couple of lookup tables for some of the valid data typing, but am undecided on the best way to implement this.

Do I use FK referential integrity, having a table with a single column which is referenced as an FK from other tables.

Or

Do I use UDF's in a check constraint to simply validate on insert?

I certainly don't want 'cascade' triggers should someone alter the lookup table, it's only meant to be 'current' valid values, not every possible value there ever was. So I feel FK isn't the right path, but your input is greatly appreciated.

1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

I guess the real answer depends on the data you are dealing with. If it's "lookup table -ish", then another table with a FK would be the best way to go. You said that the lookup table should only have current values, not every value there ever was. In this case, why don't you add every value there ever was, but also add an "IsCurrent" bit column for the current values. Sure, you may need to change your app so that it only pulls current rows for your look up tables, but that's not too bad, right?

Of course... I may be missing something too. Perhaps you could elaborate on what your data looks like.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

I have done as you suggested, and added a BIT column 'Active' , so I can pull only active values for dropdown list population, while still having referential integrity.





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music