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.

 

[sorcerer]

Tricky, sql is not normally used for that, for example you are after some form of data validation which can't normally be done easily. What front end are you using, because you could do this in a form in M$ Access for example.

 

[MarcLister]

I wonder if you might get a better response posting this in http://forums.overclockers.co.uk/forumdisplay.php?f=41 ?

 

[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]

 

[MarcLister]

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

No probs. Wasn't a criticism mate. Just suggesting that the programming forum could have had people swarming all over your thread like wasps round me in the summer.

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]

You SERIOUSLY expect moi to compare two loads of SQL code?!

 

[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.

 

[MarcLister]

Cheers Gman.