Email Contraint in SQL table

Hello,

In an attempt to lower the duplicate entry of records into my table I created constraint unique nonclustered on the email field. the problem is that my records might not have an email address so it will be null... with the current constraint i have... i cannot have multiple records with null email address... any suggestion on what can i do to modify that?
USE [Panel]
GO
/****** Object:  Index [IX__Email_tblContactInfo]    Script Date: 12/22/2009 11:43:07 ******/
ALTER TABLE [dbo].[tblContactInfo] ADD  CONSTRAINT [IX__Email_tblContactInfo] UNIQUE NONCLUSTERED 
(
	[Contact_Email] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Open in new window

AIdoHSGAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Then you can create it as an ordinary nonclustered index like

USE [Panel]
GO
/****** Object:  Index [IX__Email_tblContactInfo]    Script Date: 12/22/2009 11:43:07 ******/
ALTER TABLE [dbo].[tblContactInfo] ADD  CONSTRAINT [IX__Email_tblContactInfo]
NONCLUSTERED
(
        [Contact_Email] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
0
 
AIdoHSGAuthor Commented:
thank you rrjegan17, but that didn't work...
I was not able to leave the email field blank for two records... any other suggestions?
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Oops..IGNORE_DUP_KEY should be set to ON
Try this one..

USE [Panel]
GO
/****** Object:  Index [IX__Email_tblContactInfo]    Script Date: 12/22/2009 11:43:07 ******/
ALTER TABLE [dbo].[tblContactInfo] ADD  CONSTRAINT [IX__Email_tblContactInfo]
NONCLUSTERED
(
        [Contact_Email] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
0
 
AIdoHSGAuthor Commented:
didn't do what i was looking for... i couldn't have multiple records with null values and i could enter duplicate emails
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.