Cant make col as unique key

Question

Post reply

Cant make col as unique key

khushbu

SSCarpal Tunnel

Points: 4591
More actions
November 27, 2008 at 11:00 pm

#94843

Hi
I have a table with col_id and col_emailid and i want to make col_emailid as unique. But ,
col_id col_emailid
1 kk@abc.com
2 kk@abc.com
3 kk@abc.com
should not be allowed but
0 kk@abc.com
1/2/3 kk@abc.com
should be allowed
i.e if col_id = 0 then col_emailid can exists twice
else not.
Please help as what can i do. I cant make the two col as unique key also

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'm not sure there's a good way in SQL 2005.

Why can't you make the combination of the two columns unique?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 2

GilaMonster (11/28/2008)
I'm not sure there's a good way in SQL 2005.
Why can't you make the combination of the two columns unique?

I cant make combination as unique coz 1. abc and 2.abc is not allowed. Please read my first post carefully

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I read your first post just fine, thank you.

Is the 2-column key just because of that, or is there another reason you can't have a 2-column unique key (with different columns). You didn't say.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Try this trigger

CREATE TRIGGER dbo.trgZeroUnique ON dbo.Table1

AFTERINSERT,

UPDATE

AS

IF EXISTS (SELECT eMail FROM Table1 GROUP BY eMail HAVING COUNT(*) > 1 AND MIN(ID) > 0)

BEGIN

ROLLBACK TRANSACTION

RAISERROR('Duplicate eMail found.', 16, 1)

END

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

Jeff Moden SSC Guru Points: 1004408 More actions · Answer 5

khushbu.kumar (11/28/2008)
GilaMonster (11/28/2008)
I'm not sure there's a good way in SQL 2005.
Why can't you make the combination of the two columns unique?
I cant make combination as unique coz 1. abc and 2.abc is not allowed. [font="Arial Black"]Please read my first post carefully[/font]

Heh... pretty damned rude for someone who needs help. Stop being so bloody arrogant and you'll get a lot more help. 😉

--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)

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

BWAA-HAA! And then, you double post the same question...

http://www.sqlservercentral.com/Forums/Topic610096-146-1.aspx

... you're not making a lot of friends here.

--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)