August 16, 2004 at 9:33 am
Hello,
I am trying to migrate a database from Access to SQL Server 2000. I am having a problem with Check constraints. here's what ERWin reverse engineered from Access:
CREATE TABLE FeatureLevels (
FeatureID smallint NOT NULL DEFAULT 0,
UserLevel varchar(50) NOT NULL
CHECK (="Guest" Or ="User" Or ="Administrator"),
[Desc] varchar(255) NULL
)
go
Query Analyzer complains about the = sign, and I can clear that up. When I add UserLevel = ,like the following:
CREATE TABLE FeatureLevels (
FeatureID smallint NOT NULL DEFAULT 0,
UserLevel varchar(50) NOT NULL
CHECK (UserLevel="Guest" Or UserLevel="User" Or UserLevel="Administrator"),
[Desc] varchar(255) NULL
)
go
I get :
Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'UserLevel' references another column, table 'FeatureLevels'.
Is there a way to specify these three values for a constraint?
Thanks,
Chris
August 16, 2004 at 10:07 am
I copied your code in SQL-QA and it works for a temp table with
CREATE TABLE #FeatureLevels (
FeatureID smallint NOT NULL DEFAULT 0,
UserLevel varchar(50) NOT NULL
CHECK (Userlevel in 'Guest','User','Administrator')),
[Desc] varchar(255) NULL
)
I hope this helps.
Aidan Mooney
August 16, 2004 at 10:08 am
oops...dropped a bracket!
CREATE TABLE #FeatureLevels (
FeatureID smallint NOT NULL DEFAULT 0,
UserLevel varchar(50) NOT NULL
CHECK (Userlevel in ('Guest','User','Administrator')),
[Desc] varchar(255) NULL
)
Aidan Mooney
August 16, 2004 at 10:13 am
Yep, I just realized the ERWin left the wrong kind of quote in, and I didn't catch it. Thanks for the info.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply