Solved

Check Constraints

Posted on 1998-09-25
6
651 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:scarlett
  • 3
  • 3
6 Comments
 
LVL 2

Expert Comment

by:formula
ID: 1090272
Here's your answer:
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.


0
 

Author Comment

by:scarlett
ID: 1090273
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?
0
 
LVL 2

Expert Comment

by:formula
ID: 1090274
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.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:scarlett
ID: 1090275
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".
0
 
LVL 2

Accepted Solution

by:
formula earned 50 total points
ID: 1090276
I must take credit in leading you partially astray. Here are the steps to do what you want:

 1) EXEC sp_addtype yes_no, "char(1)", 'NULL'
 2) create rule rule_yn as @yn in ("Y","N")
 3) exec sp_bindrule rule_yn, yes_no

After this sequence, you can use the datatype "yes_no" in any table create and the rule will be applied.  You cannot use a constraint except in the context of a table create, but you can use a rule or default as above.  I suppose your original question about constraints lead me astray.  But if I redeem myself with this answer, it will be worth it.  I've actually tested and proven this on my system.


0
 

Author Comment

by:scarlett
ID: 1090277
Thank you.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now