Check constraint

Question

Post reply

Check constraint

Ezzat Metwally

SSChasing Mays

Points: 602
More actions
September 20, 2003 at 1:52 am

#176718

Hi, how can I define a check constraint for a column (not as a table level check) that references two columns, e.g.
Create Table T1 (
C1 int,
C2 int
check (C2 < C1)
)
the check is for C2
EM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

quote:
not as a table level check

Not sure of the table level check. You mean during creation, then use WITH NOCHECK

CREATE TABLE [dbo].[AAAATable1] (

[A] [int] NOT NULL ,

 [varchar] (50) NOT NULL ,

[c] [int] NOT NULL)

GO

ALTER TABLE [dbo].[AAAATable1] WITH NOCHECK ADD 

CONSTRAINT [CK_AAAATable1] CHECK ([c] >= [a])

GO

Ezzat Metwally SSChasing Mays Points: 602 More actions · Answer 2

quote:
not as a table level check

I'm migrating a DB from Sybase, in Sybase Adaptive Server, it's allowed to define a col check constraint during table creation that can reference more than one col as in my original example,

Create Table T1 (

Col1 int,

Col2 int

check (Col2 < Col1)

)

note that the check is on Col2.

But it looks like SQL Serv doesn't allow this, it complains (error 8141) that the constraint is referencing another column (Col1 in the example)

So looks like it either has to a table level constraint as in:

Create Table T1 (

Col1 int,

Col2 int,

check (Col2 < Col1)

)

notice that it's a table constraint now and not a Col2 check

or using your alter table solution

So the question is, am I missing something or SQL Serv doesn't allow more than one col to be referenced in a col check constraint?

EM

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
So the question is, am I missing something or SQL Serv doesn't allow more than one col to be referenced in a col check constraint?

You are not missing something. SQL Serv doesn't allow more than one column to be referenced in a column check constraint.

--Jonathan

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 4

quote:

CREATE TABLE [dbo].[AAAATable1] (

[A] [int] NOT NULL ,

 [varchar] (50) NOT NULL ,

[c] [int] NOT NULL)

GO

ALTER TABLE [dbo].[AAAATable1] WITH NOCHECK ADD 

CONSTRAINT [CK_AAAATable1] CHECK ([c] >= [a])

GO

If you have just created the table, why would you use the nocheck. There'd be no data to check, so it seems to me the command is superfluous. Am I missing something?

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 5

I dont think you will be able create a column level constraint that will use the values from another column. But I guess you can do this using a table constraint as already suggested by 5409045121009 though you dont have to use the NOCHECK clause...

Cheers!

Arvind

Ezzat Metwally SSChasing Mays Points: 602 More actions · Answer 6

Yes sir, I think Jonathan's reply was clear, unambiguous, and to the point, namely:-

"You are not missing something. SQL Serv doesn't allow more than one column to be referenced in a column check constraint."

Thank you all, I really like and appreciate this forum.

EM