Create Unique Index error... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create Unique Index error...

Author

Topic

yellowman
Starting Member

25 Posts

Posted - 2004-12-23 : 14:30:04

I have a small table that doesn't appear to have any duplicate values in it (there are only 30 values in the table), yet I keep getting the following errors.....

This is the script I am running:
--------------------------------------
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Users_And_Security
(
Name USER_ID_TYPE NOT NULL,
Password PASSWORD_TYPE NOT NULL,
Full_Name NAME_TYPE NOT NULL,
Description EXTRA_LONG_DESCRIPTION_TYPE NOT NULL,
Password_Option PASSWORD_TYPE NOT NULL,
Expiration_Date DATE_TIME_TYPE NOT NULL,
Password_Interval_Days SMALL_INTEGER_TYPE NOT NULL,
Functionality_List FUNCTION_LIST_TYPE NOT NULL,
Time_Stamp binary(8) NULL
)
GO
IF EXISTS(SELECT * FROM dbo.Users_And_Security)
EXEC('INSERT INTO dbo.Tmp_Users_And_Security(Name, Password, Full_Name, Description, Password_Option, Expiration_Date, Password_Interval_Days, Functionality_List, Time_Stamp)
SELECT Name, Password, Full_Name, Description, Password_Option, Expiration_Date, Password_Interval_Days, Functionality_List, Time_Stamp FROM dbo.Users_And_Security TABLOCKX')
GO
ALTER TABLE dbo.USERS_AND_SECURITY_FUNCTIONS
DROP CONSTRAINT Users_And_Security_FK
GO
DROP TABLE dbo.Users_And_Security
GO
EXECUTE sp_rename 'dbo.Tmp_Users_And_Security', 'Users_And_Security'
GO
ALTER TABLE dbo.Users_And_Security ADD CONSTRAINT
NAME_PK PRIMARY KEY CLUSTERED
(
Name
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.USERS_AND_SECURITY_FUNCTIONS WITH NOCHECK ADD CONSTRAINT
Users_And_Security_FK FOREIGN KEY
(
NAME
) REFERENCES dbo.Users_And_Security
(
Name
)
GO
COMMIT
---------------------------------------------------------------------
and here is the error I get:
---------------------------------------------------------------------
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to 'USERS_AND_SECURITY_FUNCTIONS'.
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'Bill '.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
---------------------------------------------------------------------

any help would be appreciated...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-23 : 14:43:27

Run this to see the duplicates:

SELECT [Name], COUNT(*)
FROM Users_And_Security
GROUP BY [Name]
HAVING COUNT(*) > 1

Tara

yellowman
Starting Member

25 Posts

Posted - 2004-12-23 : 14:59:25

I did that to begin with and everything had a count of one. I did not find any duplicates in the table. After I deleted 'Bill' out of the table though the script worked fine. I think somehow 'Bill' was being viewed as a duplicate even though it was only in the database once. Call it a ghost in the machine I guess....

On the other hand I have a few table that have over 2 million rows in them. Is there an easy way to delete dupes based on a clustered primary key?

Thanks for the help

Kristen
Test

22859 Posts

Posted - 2004-12-23 : 22:42:05

Is the type USER_ID_TYPE "narrower" than the datatype of the column in the original table?

If so then repeat Tara's script along the lines of:

SELECT LEFT([Name], 999), COUNT(*)
FROM Users_And_Security
GROUP BY LEFT([Name], 999)
HAVING COUNT(*) > 1

where 999 is the length of the new USER_ID_TYPE column.

USER_ID_TYPE isn't an int is it? Converting "BILL" and "JOHN" to an int is going to give you 0 in both cases ...

Kristen

TimS
Posting Yak Master

198 Posts

Posted - 2004-12-24 : 17:56:29

Run this to see the duplicates:
Modified to increase duplicate count needed if changing column type from VARCHAR to CHAR and case sensistive to NON case sensistive.

SELECT UPPER(RTRIM([Name])), COUNT(*)
FROM Users_And_Security
GROUP BY UPPER(RTRIM([Name]))
HAVING COUNT(*) > 1

Tim S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-27 : 12:32:24

For deleting duplicates, please see the deleting duplicates article found by searching the articles on the home page of sqlteam.com.

Tara

yellowman
Starting Member

25 Posts

Posted - 2004-12-28 : 11:51:53

This is the table:
--------------------

CREATE TABLE dbo.Tmp_Daily_Reading
(
RU_ID RU_ID_TYPE NOT NULL,
RU_Input_Num INPUT_NUM_TYPE NOT NULL,
Date_Time SMALL_DATE_TIME_TYPE NOT NULL,
Reading_01 READING_TYPE NOT NULL,
Status_Flag_01 DATA_STATUS_TYPE NOT NULL,
Exported_Flag_01 BOOLEAN_TYPE NOT NULL,
Reading_02 READING_TYPE NOT NULL,
Status_Flag_02 DATA_STATUS_TYPE NOT NULL,
Exported_Flag_02 BOOLEAN_TYPE NOT NULL,
Reading_03 READING_TYPE NOT NULL,
Status_Flag_03 DATA_STATUS_TYPE NOT NULL,
Exported_Flag_03 BOOLEAN_TYPE NOT NULL,
Reading_04 READING_TYPE NOT NULL,
Status_Flag_04 DATA_STATUS_TYPE NOT NULL,
Exported_Flag_04 BOOLEAN_TYPE NOT NULL,
Reading_05 READING_TYPE NOT NULL,
Status_Flag_05 DATA_STATUS_TYPE NOT NULL,
Exported_Flag_05 BOOLEAN_TYPE NOT NULL,
Time_Stamp binary(8) NULL
)
GO

This is the constraint that I am trying to put on it:
-----------------------------------------------------

ALTER TABLE dbo.Daily_Reading ADD CONSTRAINT
DAILY_READ_PK PRIMARY KEY CLUSTERED
(
RU_ID,
RU_Input_Num,
Date_Time
)
GO

This is the error I get:
---------------------------

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '001030 '.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This is the script I run to check for dupes and the part of the results. It seems every record has a duplicate.
--------------------------------------------------------------

select ru_id, ru_input_num, date_time, count(*) as Dupe_Count
from daily_reading
group by ru_id, ru_input_num, date_time
having count(*) > 1
order by count(*) desc, ru_id, ru_input_num

ru_id ru_input_num date_time Dupe_Count
001030 2 1999-07-01 00:00:00 2
001030 2 1999-06-01 00:00:00 2
001030 3 1999-06-01 00:00:00 2
001030 3 1999-07-01 00:00:00 2
001030 4 1999-06-01 00:00:00 2
001030 4 1999-07-01 00:00:00 2
001030 5 1999-07-01 00:00:00 2
001030 5 1999-06-01 00:00:00 2

This is how I tried to get rid of my dupes, but it failed I still get a dupe count of 2 on every record:
---------------------------------------------------

sp_rename 'daily_reading', 'tmp_daily_reading'

select distinct *
into Daily_Reading
from tmp_daily_reading

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 12:02:45

That is just making a copy of the table - dupes and all as it does a distinct on all columns.
I would do it like this

-- make a copy of the table just in case things go wrong
select *
into Daily_Reading_sav
from daily_reading

set rowcount 1
select 1
while @@rowcount > 0
delete daily_reading
from daily_reading
join
(select ru_id, ru_input_num, date_time
from
group by ru_id, ru_input_num, date_time
having count(*) > 1
) t
on t.ru_id = daily_reading.ru_id
t.ru_input_num = daily_reading.ru_input_num
t.date_time = daily_reading.date_time

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yellowman
Starting Member

25 Posts

Posted - 2004-12-28 : 14:28:57

I was using select distinct statement like it mentioned in one of the article on the home page of this site. I guess it didn't work. I heard I could use cursors, but I couldn't tel ya what the sytax would be. Thanks for the help.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 14:40:30

The select distinct only works if you want to put the unique index on all columns.
In your case it won't remove duplicates that are attributes of the key.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources