Unique Constraint with a twist...

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)

Unique Constraint with a twist...

Author

Topic

krugg
Starting Member

12 Posts

Posted - 2005-06-04 : 23:48:46

Hi,
I am trying to set up a unique constraint on the following table:

CREATE TABLE [tblClientAddresses] (

[AddressID] [int] NOT NULL ,
[ClientID] [int] NOT NULL ,
[AddressName] [varchar] (40) NULL ,
[Address1] [varchar] (100) NULL ,
[Address2] [varchar] (100) NULL ,
[Suburb] [varchar] (40) NULL ,
[State] [varchar] (20) NULL ,
[PostCode] [varchar] (20) NULL ,
[Country] [varchar] (100) NULL ,
[isDefault] [tinyint] NOT NULL ,
CONSTRAINT [PK_tblClientAddresses_1__13] UNIQUE CLUSTERED
(
[AddressID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Basically I need to be able to ensure that a clientID and it's "isDefault" value is NOT set to 1 more than once. That is each client can only have one record with an "isDefault" value of 1 in this table. So I thought the following:

ALTER TABLE tblClientAddresses
ADD CONSTRAINT AK_Unique_Default_Address
UNIQUE (ClientID, isDefault)

Which works.... but I need to be able to enter a clientID with an "isDefault" value set to zero more than once, so now this constraint I came up with is tripping me up!!

Is this achievable? I'm started to think not.

Thanks for any suggestions.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-06-05 : 01:08:00

Oh thee of little faith...


CREATE TABLE [tblClientAddresses] ([AddressID] [int] NOT NULL ,
[ClientID] [int] NOT NULL ,
[isDefault] [tinyint] NOT NULL,
ComputedHelper AS CASE isDefault WHEN 0 THEN -AddressID ELSE isDefault END,
CONSTRAINT [PK_tblClientAddresses_1__13] UNIQUE CLUSTERED 
(
[AddressID]
) ON [PRIMARY] 
, UNIQUE (ClientID, ComputedHelper)
) ON [PRIMARY]
go
insert tblClientAddresses
	select 1,1,0
go
insert tblClientAddresses
	select 2,1,1
go
insert tblClientAddresses
	select 3,1,0
go
insert tblClientAddresses
	select 4,1,1
GO
SELECT * from tblClientAddresses

DavidM

A front-end is something that tries to violate a back-end.

krugg
Starting Member

12 Posts

Posted - 2005-06-05 : 02:09:13

I feel small...

Obi Wan has taught you well... my jedi!

Thank you, learn I will (in a yoda voice)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources