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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy