Ensure Unique Value Across Records

Question

Post reply

Ensure Unique Value Across Records

david.c.holley

SSCrazy

Points: 2423
More actions
October 7, 2009 at 5:03 pm

#211864

I'm building an ASP.NET telephone directory for my organization. The idea is to include the contact information for actual company employees as well as contract employees and those of business partners. Also, the idea is to present a user with the ability to update their own information by grabbing their Windows User Id and using that to locate their information.
So we have a situation were not all of the records in the table will have an associated Windows User Id. The contract employees & business partners will never have one and not all company employees have one.
1) How do I designate that a column in SQLServer should not allow duplicate values?
2) I'm assuming that SQL Server recognizes Null and thus multiple records with Null would not be allowed thus requiring the Windows User Ids to be held in a separate table. Correct?
Here's the primary table as it currently exists. TFCUserId is the column originally conceived as holding the Windows User Id.
USE [CentralEventInformation]
GO
/****** Object: Table [dbo].[Contacts] Script Date: 10/07/2009 18:58:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contacts](
[ContactId] [int] IDENTITY(1000,1) NOT NULL,
[TFCUserId] [varchar](15) NULL,
[NameFirst] [varchar](20) NOT NULL,
[NameLast] [varchar](20) NOT NULL,
[NameMiddleInitial] [nchar](1) NULL,
[NameInitials] [varchar](3) NULL,
[TitleId] [int] NULL,
[PhoneDirect] [varchar](15) NULL,
[PhoneMobile] [varchar](15) NULL,
[PhoneFax] [varchar](15) NULL,
[PhoneDirectConnect] [varchar](30) NULL,
[PhoneExtension] [varchar](5) NULL,
[PhoneDirectTemporary] [varchar](15) NULL,
[PhoneMobileTemporary] [varchar](15) NULL,
[PhoneFaxTemporary] [varchar](15) NULL,
[PhoneDirectConnectTemporary] [varchar](30) NULL,
[PhoneExtensionTemporary] [varchar](5) NULL,
[PhoneTemporaryInformationExpirationDate] [date] NULL,
[PhoneUseTemporaryInformation] [bit] NULL,
[CompanyFacilityId] [int] NULL,
[BranchId] [int] NULL,
[DepartmentId] [int] NULL,
[Message] [varchar](1000) NULL,
[MessageExpirationDate] [date] NULL,
[IsFacilityGeneralNumber] [bit] NOT NULL,
[FacilityGeneralNumberDescription] [varchar](50) NULL,
[NumberOfDelegates] [int] NOT NULL,
[TimeZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF_Contacts_IsFacilityGeneralNumber] DEFAULT ((0)) FOR [IsFacilityGeneralNumber]
GO
ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [DF_Contacts_NumberOfDelegates] DEFAULT ((0)) FOR [NumberOfDelegates]
GO
ALTER TABLE [dbo].[Contacts] ADD DEFAULT ((0)) FOR [TimeZoneId]
GO
If the User Ids have to be in an external table, this is what I'm thinking. Not that both fields would have to enforce unique values individually to ensure that only 1 contact is associate with only 1 windows id.
If a second table is neccessary, the examples below show the valid/invalid combinations of values.
TFCUserID ContactID
fsl1 3280
fsl1 4300 Invalid - fsl1 is already associated with Contact #3280
bsh3 3280 Invalid - Contact #3280 is already associated with an user id
USE [CentralEventInformation]
GO
/****** Object: Table [dbo].[ContactUserIds] Script Date: 10/07/2009 18:59:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContactUserIds](
[TFCUserId] [varchar](15) NOT NULL,
[Contactid] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Viewing 9 posts - 1 through 8 (of 8 total)

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

david.c.holley SSCrazy Points: 2423 More actions · Answer 1

I just answered my own question...

Alter table contactuserids

Add constraint ix_WindowsUserId UNIQUE(WindowsUserID)

Alter table contactuserids

Add constraint ix_ContactId Unique(ContactId)

I just didn't know what the term was.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

david.c.holley (10/7/2009)
I just answered my own question...
Alter table contactuserids
Add constraint ix_WindowsUserId UNIQUE(WindowsUserID)
Alter table contactuserids
Add constraint ix_ContactId Unique(ContactId)
I just didn't know what the term was.

Keep in mind that UNIQUE constraints will all one null.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.c.holley SSCrazy Points: 2423 More actions · Answer 3

Jeff Moden (10/7/2009)
david.c.holley (10/7/2009)
I just answered my own question...
Alter table contactuserids
Add constraint ix_WindowsUserId UNIQUE(WindowsUserID)
Alter table contactuserids
Add constraint ix_ContactId Unique(ContactId)
I just didn't know what the term was.
Keep in mind that UNIQUE constraints will all one null.

Yep, caught that. I ended up spinning things out to a second table to allow for scenarios where a person doesn't have a windows user id.

david.c.holley SSCrazy Points: 2423 More actions · Answer 4

david.c.holley (10/7/2009)
Jeff Moden (10/7/2009)
david.c.holley (10/7/2009)
I just answered my own question...
Alter table contactuserids
Add constraint ix_WindowsUserId UNIQUE(WindowsUserID)
Alter table contactuserids
Add constraint ix_ContactId Unique(ContactId)
I just didn't know what the term was.
Keep in mind that UNIQUE constraints will all one null.
Yep, caught that. I ended up spinning things out to a second table to allow for scenarios where a person doesn't have a windows user id.

Is there a way to do this AND allow Nulls? In other words, the when the WindowsUserId is provided, it must be unique, but it may not exist hence it could be Null?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

david.c.holley (3/19/2010)
Is there a way to do this AND allow Nulls? In other words, the when the WindowsUserId is provided, it must be unique, but it may not exist hence it could be Null?

Yes, use a filtered non-clustered unique index.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

SwePeso (3/21/2010)
david.c.holley (3/19/2010)
Is there a way to do this AND allow Nulls? In other words, the when the WindowsUserId is provided, it must be unique, but it may not exist hence it could be Null?
Yes, use a filtered non-clustered unique index.

Just remember... that allows one and only one NULL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Are you sure, Jeff?

If you filter for the nullable/unique column

CREATE UNIQUE NONCLUSTERED INDEX UX_Table1 ON Table1 (Col1)

WHERE Col1 IS NOT NULL

You can have how many NULL's you want, but as soon as the value is non-null it has to be unique.

N 56°04'39.16"
E 12°55'05.25"

Paul White SSC Guru Points: 150468 More actions · Answer 8

SwePeso (3/21/2010)
david.c.holley (3/19/2010)
Is there a way to do this AND allow Nulls? In other words, the when the WindowsUserId is provided, it must be unique, but it may not exist hence it could be Null?
Yes, use a filtered non-clustered unique index.

Absolutely. This is one of the new features in SQL Server 2008 that I like most.

The problem of unique-but-only-for-non-NULLs has been around for years, so it is good to see that SQL Server finally includes support for it, even if it is 2008-specific.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi