?
Solved

Email Contraint in SQL table

Posted on 2009-12-22
5
Medium Priority
?
178 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:AIdoHSG
  • 2
  • 2
4 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1500 total points
ID: 26106200
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
 

Author Comment

by:AIdoHSG
ID: 26106886
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26109478
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
 

Author Closing Comment

by:AIdoHSG
ID: 31669060
didn't do what i was looking for... i couldn't have multiple records with null values and i could enter duplicate emails
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question