Solved

Limiting the Possible Values in a Text Column

Posted on 2004-08-30
6
260 Views
Last Modified: 2011-10-03
I have a column in a 2000 SQL Server Database that is of the text data type.  I would like to restrict the possible values to either 'blue', 'red', or 'green'.  Any help is appreciated, and I will reward points quickly.
0
Comment
Question by:LucasWhite84
  • 3
  • 2
6 Comments
 

Author Comment

by:LucasWhite84
ID: 11932639
In other words, I want the database to have a constraint where it won't allow any more values other than 'blue', 'red', and 'green' to be placed in this column.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11932727
You can use hte following script - modifying fro your table / column names of course :

alter table mytable add constraint [CK_mytable]  CHECK ([text_field] = 'red' or [text_field] = 'green' or [text_field] = 'blue')
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 400 total points
ID: 11932746
Or you can do it using Enterprise Manager - right click on table -> design table
in the table designer, right-click and select Check Constraints
there you acn enter the constraint name e.g. CK_mytable, and the text e.g. [text_field] = 'red' or [text_field] = 'green' or [text_field] = 'blue'
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:LucasWhite84
ID: 11932878
I tried to run the script and it said...

Server: Msg 1760, Level 16, State 1, Line 1
Constraints of type CHECK cannot be created on columns of type TEXT.

Then, I tried to type in [Color] = 'red' or [Color] = 'green' or [Color] = 'blue' in the Enterprise Manager constraint entry and it said that there was an error with the constraint.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 11932983
>>Constraints of type CHECK cannot be created on columns of type TEXT.<<
If all you need to save is red green or blue.  Than you should not be using TEXT data type in the first place.  Use varchar(5) instead.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11933007
Yes, as the error says, Constraints of type CHECK cannot be created on columns of type TEXT.
Sorry, I didn't notice that you had text data types.
TEXT data types are really only for large text strings (normally > 8000 characters) they have a lot of limitations and are really desinged only for extra long strings.
what you want is data type char or varchar. You should change your column type to something like varchar(5) if the only valid values are 'red'. 'blue' or 'green'
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

896 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

11 Experts available now in Live!

Get 1:1 Help Now