create table uniquefoo
(
	col1 int null,
	col2 varchar(5)
)
go
insert uniquefoo values(1, 'foo1')
insert uniquefoo values(null, 'foo2')
insert uniquefoo values(null, 'foo3')
insert uniquefoo values(4, 'foo4')
go
-- alter table uniquefoo
-- add constraint myunique_constraint unique(col1)
create unique index myunique_index
on uniquefoo(col1)

quote:

---

Originally posted by lfmn

I have a table which has a field called WBS. The field allows nulls, but other than the nulls, the value should not be repeated

---

quote:

---

SQL is useful if you don't know cursors :-)

---

quote:

---

Originally posted by X002548

Not true...you can't define a primary key..but you can have a unique index...did you try it?



Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

---

quote:

---

Originally posted by jsmith8858

so maybe empty out the table to a temp location (use SELECT INTO ... syntax), add the unique index, and then re-populate?



- Jeff

---

create table uniquefoo
(
	col1 int null,
	col2 varchar(5),
	col3 int identity,
        col4 as (case when col1 is null then col3 else col1 end)
)
go

create unique index myunique_index
on uniquefoo(col4)

go

-- these all work:
insert uniquefoo values(1, 'foo1')
insert uniquefoo values(null, 'foo2')
insert uniquefoo values(4, 'foo4')

-- this works too:
insert uniquefoo values(null, 'foo3')

-- but this doesn't work:
insert uniquefoo values(4, 'foo5')


select * from uniquefoo
go
drop table uniquefoo

quote:

---

Originally posted by lfmn
I did try it. Did you? If you had, you would have received an error similar to this:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 12. Most significant primary key is '<NULL>'.
The statement has been terminated.

I would recommend checking your facts before answering a post.

SQL is useful if you don't know cursors :-)

---

quote:

---

Originally posted by jsmith8858

uhh .. Brett .... he did what you told him to do and reported back the error. the error is consistent with his original post, where he said there are multiple Null values in a column, but he wants a unique index on all non-null values.

- Jeff

---

quote:

---

I have a table which has a field called WBS. The field allows nulls, but other than the nulls, the value should not be repeated. I can't use a unique index because of the nulls.

---

quote:

---

I would recommend checking your facts before answering a post.

---

quote:

---

I feel like you really don't deserve much help,

---

quote:

---

Repopulate with the same values that would not let you create the index? When re-entering the values, you would get an error message from the index you just created because the values you are entering are not unique.

---

quote:

---

1 sack Slap for Brett

---

quote:

---

Originally posted by jsmith8858

First off, I was just responding to the other posts.

I feel like you really don't deserve much help, but I'll give you an idea anyway.



- Jeff

---

quote:

---

Originally posted by jsmith8858



What offends me is what you said to Brett. I love how people here ask for help and give people shit when they try to help you.

ARRGGGGHHHH ... sorry had to vent. feel better now. Good luck out there, kid.

- Jeff

---

quote:

---

Second, Brett shot from the hip and gave an incorrect answer. It would have taken him all of 1 minute to do a quick check, but apparently that was too much trouble. If he was really trying to help he would have been more careful.
...
I used to read this forum every day a few years ago. I was away for a long time and I recently posted a few questions and got lots of posts, but few answers.
...
If you don't know the answer, DON'T POST! There is no shame in not knowing everything, but don't screw up someone else just to try and make yourself look like a big man.

---

quote:

---

My pet peeve is people who post just to read their brilliance on the internet

---

quote:

---

Any other thoughts?

---

quote:

---

Originally posted by Merkin

Ignore him Jeff

lfmn has been an asshole here for a few years, I don't think he will change now.



Damian

---