scarlett
asked on
Check Constraints
The documentation for MS SQL7 recommends using check constraints rather than rules.
1. Can I impose a check constraint on a user-defined data type? If so how - add the syntax to this statement to force either 'Y','N', or '' as acceptable values:
EXEC sp_addtype yes_no, char(1), 'NULL'
2. In a table definition can a field-level check constraint refer to a field in a general lookup table (this is not a foreign key candidate)? If so please provide specific syntax.
3. Opinion only - why would a check constraint be preferable to a rule?
1. Can I impose a check constraint on a user-defined data type? If so how - add the syntax to this statement to force either 'Y','N', or '' as acceptable values:
EXEC sp_addtype yes_no, char(1), 'NULL'
2. In a table definition can a field-level check constraint refer to a field in a general lookup table (this is not a foreign key candidate)? If so please provide specific syntax.
3. Opinion only - why would a check constraint be preferable to a rule?
ASKER
My interest in question #1 is to globally associate the values with the user-defined data type so that I don't have to declare them each time I place the field in another table. Is this possible? If so, what is the syntax?
Just wanted to respond and say there is no way to store the values of the datatype within the datatype definition itself. It is the rule or constraint that associates the values. You only have to create a rule once, but you still have to bind the rule to a column each time you create it.
ASKER
Interesting that you can bind a default to a user-defined data type but not the ability to check for acceptable values.
I'd like to give you credit for your answer and close this question, formula - could you send me an "answer" instead of a "comment".
I'd like to give you credit for your answer and close this question, formula - could you send me an "answer" instead of a "comment".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
1)create your user defined datatype:
EXEC sp_addtype yes_no, 'char(1)', NULL
use it in a table and add contraint (this is table level):
create table test( YNcolumn yes_no)
alter table test add constraint YN check
(YNcolumn in ('Y','N'))
OR
Add constraint at table create time(This is column level):
create table test(
YNcolumn yes_no constraint YN check (YNcolumn in ('Y','N')))
2)A check constraint cannot compare to values in another table. You should use a trigger to do this.
3) Rules are transact SQL and not ANSI supported. Check constraints are ANSI SQL, which would allow for more portability to other ANSI supported platforms.