Unique identifier column has duplicate values.

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

General SQL Server Forums

New to SQL Server Programming

Unique identifier column has duplicate values.

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2012-04-27 : 00:05:28

Hi,

Table "ts_order_charge_audit" have a column called "ordercharge_id" which If I look at it says "(PK, uniqueidentifier, NOT NULL)".

See a screen shot -
http://screencast.com/t/CDGJiD5C3xSR

but it has duplicate entries on that column.

I do the following and get a lot of results.


select ordercharge_id, COUNT(ordercharge_id) 
from ts_order_charge_audit
group by ordercharge_id
HAVING (COUNT(ordercharge_id) > 1)

How is this possible?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-04-27 : 00:52:41

is that column part of a composite PK? that is, maybe there are 2 or more cols that form the PK?

elsasoft.org

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-27 : 02:09:27

There is absolutely no way this column is the only PK-column and that it has duplicates. Post a screenshot of the entire table and I'm pretty sure there is another key-column in there.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2012-04-27 : 13:34:29

You both are right. There is another column and it is a composite key.

This is a script that takes some data from several tables, change the schema of the table and put the data back to the tables.

Now after it does that, it is doing the following. Adding the constaint back on and it errors out.

ALTER TABLE [dbo].[ts_order_charge_audit] ADD CONSTRAINT [PK_ts_order_charge_audit] PRIMARY KEY CLUSTERED
(
[ordercharge_id] ASC,
[ordercharge_version] ASC
)

My question - is there query handy I can run to see if there are any rows that have the combination of [ordercharge_id] and ordercharge_version] same?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-04-27 : 13:40:22

sure, you already wrote most of it in your first post:

select ordercharge_id, ordercharge_version, COUNT(*)
from ts_order_charge_audit
group by ordercharge_id, ordercharge_version
HAVING (COUNT(*) > 1)

elsasoft.org

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2012-04-27 : 13:40:26

Sorry. Not sure if i asked the question right.

To rephrase is:

If a table has COLUMN1 and COLUMN2, how can I return the rows that has the combination of COLUMN1 and COLUMN2 repeated, which is a duplicate.

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2012-04-27 : 14:19:58

Thanks Jezz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources