Restricting values for a column

[sineados]

Hello there,

I was wondering if its possible to restrict what a user can enter in to a column in a table.

I am using SQL Server 2000 with an Access front end. I know in Access you can use the lookup wizard which shows a drop down of the valid values but when I exit the database its not saved.

thanks in advance,
Sinead

 

[dbtech]

U can use CHECK constraints in SQL Server to limit the values entered into a column, note that when the violation of constraint occurs SQL Server will issue a warning message The message will not be in user-friendly format,
Which u will need to trap in your access front end and display a proper message.

You can also make use of triggers to limit the type of values entered into a column. constraints are prefered over trigger for performance. Constraints are fired before the trigger fires.

If you have SQL BOL(Books On Line) check for CHECK Constraints and Triggers


Example from BOL

CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)

in the above sql statement, look at the statements in bold,
this are constraints.

dbtech

 

[sineados]

that worked great. thanks a mill. I would not have thought of contraints.