adding UNIQUE Constraint to existing column

Hello,
I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
duplicate key using WITH NOCHECK in SQL Server 2000.
Here is my SQL syntax:
ALTER TABLE user_email WITH NOCHECK
ADD CONSTRAINT unq_user_email_email UNIQUE (email)
Query Analyzer keeps giving me the error message that duplicate key was foun
d:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 4. Most significant primary key is 'user1@.abc.com'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
I learned from Books Online that "WITH NOCHECK" allows to add an unverified
constraint and prevents the validation against existing rows.
I tried using SQLServer Enterprise Manager, Manage Indexes and got the same
error message even though that I had checked the "Ignore Duplicate Values"
checkbox.
Can someone please tell me how i can add a UNIQUE CONSTRAINT without
removing the duplicate keys.
Thank you so much for your help!
--
MitraSQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
index does not allow duplicates, under no circumstances.
The following text is also part of the BOL article:
"The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
and UNIQUE constraints."
HTH,
Gert-Jan
mitra wrote:
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
> Query Analyzer keeps giving me the error message that duplicate key was fo
und:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
> I learned from Books Online that "WITH NOCHECK" allows to add an unverifie
d
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the sam
e
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
Thankfully you can't. From BOL:
"IGNORE_DUP_KEY
Controls what happens when an attempt is made to insert a duplicate key
value into a column that is part of a unique clustered index. If
IGNORE_DUP_KEY was specified for the index and an INSERT statement that
creates a duplicate key is executed, SQL Server issues a warning and ignores
the duplicate row."
When it says ignores the duplicate row, it means it doesn't insert it.
If you want to do what you are suggesting you will need to build a trigger.
Then just join to the parent table to see if the values already exist (make
sure to take care of the case where duplicate values are inserted via the
INSERT statement. If you want more information about how to do this, I
could help for out (as could others here :).
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:3BB0328C-8FC1-40A8-A614-D8E2C0B43859@.microsoft.com...
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
>
> Query Analyzer keeps giving me the error message that duplicate key was
> found:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I learned from Books Online that "WITH NOCHECK" allows to add an
> unverified
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the
> same
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:

> SQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
> index does not allow duplicates, under no circumstances.
> The following text is also part of the BOL article:
> "The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
> and UNIQUE constraints."
I guess to achieve the effect you want, you could use a trigger:
create table user_email (email varchar(20) not null)
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
go
select * from user_email
go
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT
AS
IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
inserted.email)
BEGIN
RAISERROR ('Email must be unique',16,1)
ROLLBACK TRANSACTION
END
go
insert into user_email values ('rpresser@.nowhere.com')
Results in this output:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
email
--
rpresser@.imtek.com
rpresser@.imtek.com
rpresser@.imtek.com
(3 row(s) affected)
Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
Email must be unique|||Not quite. Two problems. First, the trigger fires after the insert. No
matter what you insert it will fail because whatever is in the inserted
table will already be in the table. Second, you are forgetting the case
where the user inserts duplicates in the insert statement:
insert into user_email
select 'bob@.bob.com'
union
select 'bob@.bob.com'
Here you have to consider the uniqueness of the values in the inserted
table.
Using his table, this code will work:
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT, UPDATE
AS
IF EXISTS ( select user_email.email
FROM inserted
join user_email
on user_email.email = inserted.email
group by user_email.email
having count(*) > 1 )
begin
RAISERROR ('Email must be unique.',16,1)
ROLLBACK TRANSACTION
return
end
go
Note that I changed it to an UPDATE trigger also, since otherwise:
UPDATE user_email
SET email = 'fred@.server.com'
would work just fine. I would still suggest against this approach. Clean
up the data and life will be peachy :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:l5jr9dfyygw7$.dlg@.rpresser.invalid...
> On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:
>
> I guess to achieve the effect you want, you could use a trigger:
> create table user_email (email varchar(20) not null)
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> go
> select * from user_email
> go
> CREATE TRIGGER user_email_add
> ON user_email
> FOR INSERT
> AS
> IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
> inserted.email)
> BEGIN
> RAISERROR ('Email must be unique',16,1)
> ROLLBACK TRANSACTION
> END
> go
> insert into user_email values ('rpresser@.nowhere.com')
>
> Results in this output:
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> email
> --
> rpresser@.imtek.com
> rpresser@.imtek.com
> rpresser@.imtek.com
> (3 row(s) affected)
> Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
> Email must be unique