constraint to check another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

constraint to check another column

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 09:58:51

Any ideas on how to add a constraint/rule to check another column?
If 1 column = 12, I need it to make sure that another column is not null

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 10:03:53

Yes. Add table level check constraint.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 10:10:00

yes but that only checks specific conditions doesn't it?
I need a case statement or something
CHECK (InvoiceStatus = 12)

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 10:18:21

got it:
CHECK (
supplier.city IN ('NEW YORK', 'BOSTON', 'CHICAGO')),
CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20)

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 10:21:50

how do you do it with an existing column?

ALTER TABLE Invoices
ALTER COLUMN InvoiceStatus CHECK (
InvoiceStatus = 12),
CHECK (QueryID IS NOT NULL)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 10:47:54

[code]Alter table <table>
Add Constraint <constraint-name> Check (<expression>)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 10:53:35

ALTER TABLE Invoices
ADD CONSTRAINT QueryIDIntegrity CHECK (
InvoiceStatus = 12),
CHECK (QueryID IS NOT NULL)

not quite sure why I get this error:
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN CHECK constraint 'QueryIDIntegrity'. The conflict occurred in database 'CCApp_DEV', table 'Invoices', column 'InvoiceStatus'.

The check looks like it's checking for the values regardless of each other hence the error?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 11:20:55

[code]Alter Table dbo.test
Add Constraint chk_1 Check (a = 12 and b is not null)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 11:33:17

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE CHECK constraint 'QueryIDIntegrity'. The conflict occurred in database 'CCApp_DEV', table 'Invoices'.
ALTER TABLE Invoices
ADD CONSTRAINT QueryIDIntegrity CHECK (InvoiceStatus = 12 AND QueryID IS NOT NULL)

hmm, lost

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 11:37:50

You need to drop the previous constraint before adding new one

ALTER TABLE Invoices
Drop Constraint <constraint-name>
GO

ALTER TABLE Invoices
ADD CONSTRAINT QueryIDIntegrity CHECK (InvoiceStatus = 12 AND QueryID IS NOT NULL)
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 11:42:28

there are only foreign key constraints. do I have to drop those as well?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 11:44:17

Script your table defintion along with all constraints and post it here.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 11:47:16

CREATE TABLE [dbo].[InvoiceStatus] (
[ID] [int] NOT NULL ,
[Description] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessDay] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoices] (
[invoice_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cust_id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_postcode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[credit_contname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[credit_cont_tel] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[project_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inv_date] [datetime] NULL ,
[due_date] [datetime] NULL ,
[amount] [money] NULL ,
[type] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[totamnt] [money] NULL ,
[projman] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[projmanContactID] [int] NULL ,
[projdesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contact] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contactID] [int] NULL ,
[costcent] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company_code] [int] NULL ,
[PaidFlag] [bit] NOT NULL ,
[PaidDate] [datetime] NULL ,
[InvoiceStatus] [int] NOT NULL ,
[SystemFollowUpDate] [datetime] NULL ,
[AmendedFollowUpDate] [datetime] NULL ,
[NextActionDue] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDateTime] [datetime] NOT NULL ,
[LUpdate] [datetime] NULL ,
[LProg] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PromAmt] [money] NULL ,
[PromAmtDate] [datetime] NULL ,
[Promised] [bit] NOT NULL ,
[PartPromised] [bit] NOT NULL ,
[Legal] [bit] NOT NULL ,
[QueryFlag] [bit] NOT NULL ,
[QueryID] [int] NULL ,
[QueryType] [int] NULL ,
[UpdateFlag] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[InvoiceStatus] WITH NOCHECK ADD
CONSTRAINT [PK_InvoiceStatus] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoices] WITH NOCHECK ADD
CONSTRAINT [DF_Invoices_PaidFlag] DEFAULT (0) FOR [PaidFlag],
CONSTRAINT [DF_Invoices_InvoiceStatus] DEFAULT (0) FOR [InvoiceStatus],
CONSTRAINT [DF_Invoices_CDateTime] DEFAULT (getdate()) FOR [CDateTime],
CONSTRAINT [DF_Invoices_Promised] DEFAULT (0) FOR [Promised],
CONSTRAINT [DF_Invoices_PartPromised] DEFAULT (0) FOR [PartPromised],
CONSTRAINT [DF_Invoices_Legal] DEFAULT (0) FOR [Legal],
CONSTRAINT [DF_Invoices_QueryFlag] DEFAULT (0) FOR [QueryFlag],
CONSTRAINT [DF_Invoices_UpdateFlag] DEFAULT (0) FOR [UpdateFlag],
CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
(
[invoice_no]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoices] ADD
CONSTRAINT [FK_Invoices_InvoiceStatus] FOREIGN KEY
(
[InvoiceStatus]
) REFERENCES [dbo].[InvoiceStatus] (
[ID]
)
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 11:50:46

I ran the code you posted at my end and also added check constraint. Runs perfectly fine!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 12:04:25

Must be something to do with the existing data then ?

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 12:08:47

I added with nocheck and seemed to work fine
ALTER TABLE Invoices with nocheck
ADD CONSTRAINT QueryIDIntegrity CHECK (Invoices.InvoiceStatus = 12 AND Invoices.QueryID IS NOT NULL)

However, the expression is wrong as it won't let me insert any data at all. When I update the status to 0 it fails.
So, it must be looking at the existing data and finding it doesn't meet that CHECK.
The CHECK needs to be specifically for that cdriteria and everything else should be passable
?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 12:15:05

May be this is what you want after all:

ALTER TABLE Invoices
ADD CONSTRAINT QueryIDIntegrity CHECK ((Invoices.InvoiceStatus = 12 AND Invoices.QueryID IS NOT NULL) OR Invoices.InvoiceStatus <> 12)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-21 : 12:23:24

perfect. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources