SQL server writing constraints

[Gman]

I've got a table and i'm wanting to add a constraint so that a record must contain either a clientID or a userNumber but they can't both be empty.

for example

CREATE TABLE [dbo].[ClientIndex] (
[SequenceID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [ClientIDType] NOT NULL ,
[Surname] [String40] NULL ,
[SurnameSoundex] [SoundexType] NULL ,
[Firstname] [String40] NULL ,
[FirstnameSoundex] [SoundexType] NULL ,
[Title] [String10] NULL ,
[Gender] [Sex_type] NULL ,
[DateOfBirth] [datetime] NULL ,
[userNumber][integer] NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

I need there allways be either a ClientID or a userNumber , both cannot be NULL but they can both be populated if needed.

Any ideas how I go about writing this constraint.

 

[Gman]

lol sorry guys yep this was meant for the programming forum not sure how it ended up in here

Anywhooo I managed to sort it yesterday, it just required altering to the following


CREATE TABLE [dbo].[ClientIndex] (
[SequenceID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [ClientIDType] NOT NULL ,
[Surname] [String40] NULL ,
[SurnameSoundex] [SoundexType] NULL ,
[Firstname] [String40] NULL ,
[FirstnameSoundex] [SoundexType] NULL ,
[Title] [String10] NULL ,
[Gender] [Sex_type] NULL ,
[DateOfBirth] [datetime] NULL ,
[userNumber][integer] NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) WITH FILLFACTOR = 90 ON [PRIMARY],
CONSTRAINT [CID_UserNUM] CHECK
(
([ClientID] IS NOT NULL AND [userNumber] IS NULL) OR ([ClientID] IS NULL AND [userNumber] IS NOT NULL)
)
) ON [PRIMARY]

 

[Gman]

lol sorry about that , same code with the change highlighted

CREATE TABLE [dbo].[ClientIndex] (
[SequenceID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [ClientIDType] NOT NULL ,
[Surname] [String40] NULL ,
[SurnameSoundex] [SoundexType] NULL ,
[Firstname] [String40] NULL ,
[FirstnameSoundex] [SoundexType] NULL ,
[Title] [String10] NULL ,
[Gender] [Sex_type] NULL ,
[DateOfBirth] [datetime] NULL ,
[userNumber][integer] NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) WITH FILLFACTOR = 90 ON [PRIMARY],
CONSTRAINT [CID_UserNUM] CHECK
(
([ClientID] IS NOT NULL AND [userNumber] IS NULL) OR ([ClientID] IS NULL AND [userNumber] IS NOT NULL)
)
) ON [PRIMARY]

Oh and sorcerer nearly all sql server database have constraints like this be having these it ensure they data is entered exactly how you want even if you edit the db directly insted of through the application.