SQL, Unique Values but Allow NULLS

[tbg130]

Hi All,

I'm just starting to get in to SQL 2005 and have learned alot. I have a database that has just a few thousand records in it but I'd like to eliminate the possibility of having a duplicate entry of one of the fields WHILE ALLOWING Null values.

I read on constraints and it said that you could do a constraint to allow no duplicates in a field BUT ALLOW nulls; it didn't work for me.

Any suggestions and step-by-steps on how to do this would be much appreciated...

Thanks in advance.

 

[Luzian]

I know this doesn't answer your question, but.. I hate nulls. I normalize my database so I don't need them.

 

[Luzian]

For example, instead of the followed table:

CREATE TABLE [Customers]
(
	[id] int NOT NULL IDENTITY PRIMARY KEY,
	[name] varchar(50) NOT NULL UNIQUE, -- username
	[password] varchar(50) NOT NULL,
	[email] varchar(100) UNIQUE
)

I'd use these two tables

CREATE TABLE [Customers]
(
	[id] int NOT NULL IDENTITY PRIMARY KEY,
	[name] varchar(50) NOT NULL UNIQUE, -- username
	[password] varchar(50) NOT NULL
)

CREATE TABLE [Emails]
(
	[customer_id] int NOT NULL FOREIGN KEY REFERENCES [Customers]([id]),
	[data] varchar(100) NOT NULL UNIQUE -- email address
)

 

[r937]

luzian, if you wanted to produce a list of customers, including their emails, you'd use a LEFT OUTER JOIN, right?

and if a customer doesn't have a matching row in the Emails table, what do you get in the result set in the (strangely renamed) "data" column?

that's right, a NULL

seems you can't get away from them after all, eh



r937.com | rudy.ca

 

[Luzian]

viewing them is not so bad. Storing them is another story. I beleive in the theory of storing data, not the absence of it. It's similar to how I wouldn't store converted data in the database if it can be converted after returning results, for example: storing HTML formatting for a column that is implicitly known to be formatted in a particular way. In other words, data display is left for the presentation layer, not the data layer.

 

[Luzian]

And it also eliminates his problem with nulls on a unique column.

 

[r937]

thanks luzian, yes, your rationale is excellent

i just didn't know how much you knew about the problems of nulls, because you have asked quite a number of newbie-type questions in this forum recently

r937.com | rudy.ca

 

[Luzian]

If I came off as snobbish, I apologize. I tend to be exact when it come to things I do know, it's my INTP personality type. I'm a realistic person, I know that when it comes to SQL, you're overall knowledge is greater than mine. I'm not here to challange your expertise. Yes I am a newbie at SQL, but I am exceptionally good at other things. You've answered a few of my questions, and I appreciate them, my supposed internet tone is a bit weird, but I don't mean any offense.

 

[r937]

none taken ;-)

r937.com | rudy.ca

 

[tbg130]

Thanks for the input; when in SQL 2005 and trying to do this visually, I get the following error code:

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.SampleData' and index name 'IX_IPLow'. The duplicate key value is (<NULL>).
The statement has been terminated.

Way around this?

Thanks.

 

[Luzian]

As far as I know, SQL Server doesn't let you.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=25259&DisplayTab=Article

But you could use my method to avoid this problem.