Prev: Logical Error Very simple I'm sure!
Next: Update Column with Restricted Collation?
From: msnews.microsoft.com on 13 Apr 2010 09:57
I have a table that contains PLSS information and want to restrict the
columns to certain values. Since there is a pattern in what they are
restricted to, I wonder if there would be an easier way than to create a
lookup table and use a constraint.

For instance, my values for one field is limited to 3 characters: from
01-49, with the third character always an 'E' or 'W'

Would this be easier done in a query or stored procedure or function than to
create a lookup table?

Thanks for your help.


From: Plamen Ratchev on 13 Apr 2010 12:27
In the case you describe you can use a CHECK constraint:

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
datacol CHAR(3) CHECK (datacol LIKE '[0-4][1-9][EW]'));

--
Plamen Ratchev
http://www.SQLStudio.com
From: Gert-Jan Strik on 13 Apr 2010 12:44
"msnews.microsoft.com" wrote:
>
> I have a table that contains PLSS information and want to restrict the
> columns to certain values. Since there is a pattern in what they are
> restricted to, I wonder if there would be an easier way than to create a
> lookup table and use a constraint.
>
> For instance, my values for one field is limited to 3 characters: from
> 01-49, with the third character always an 'E' or 'W'
>
> Would this be easier done in a query or stored procedure or function than to
> create a lookup table?
>
> Thanks for your help.

A CHECK constraint could be a good move. Assuming that "40E" should be a
valid value too, I would suggest something like this:

CHECK (datacol LIKE '0[1-9][EW]' OR datacol LIKE '[1-4][0-9][EW]')

--
Gert-Jan
 | 
Pages: 1
Prev: Logical Error Very simple I'm sure!
Next: Update Column with Restricted Collation?