2005 will not allow me to set Primary Key again on same column

Sql Server 2005 table, a not null nvarchar column, in table design had made
column a Primary Key with "Set Primary Key" menu option.
At a later time, in table design, used "Remove Primary Key" menu option on
the column to remove having column as a Primary Key.
Now I need to make the column a Primary Key again. In table design right
click menu "Set Primary Key", I click it and then Save table to save the
change... I receive this message:
'mytablename (dbo)' table
- Unable to create index 'PK_mytablename'.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was
found for the object name 'dbo.mytablename' and the index name
'PK_mytablename'. The duplicate key value is (11).
Could not create constraint. See previous errors.
The statement has been terminated.
I am prevented from making the column a Primary Key. And I'm assuming this
is because I had previously made it a Primary Key. But - I did remove it
using the table design menu so shouldn't I be able to redo it?
I have looked all over the indexes and constraints and ran various querys
I've found from googliing and nothing turns up that confirms this error
message. Meaning I can't find any constraint or index or anything else on the
table and/or column.
I can't find anything that validates the message is correct.
How do I resolve this error and be able to successfully move past it and be
able to successfully set my column as a Primary Key?> How do I resolve this error and be able to successfully move past it and
> be
> able to successfully set my column as a Primary Key?
The duplicate key error message indicates that you have more than one row in
the table with the same proposed primary key value. You'll need to either
delete the duplicate rows or change existing data to avoid dups. A query
like the one below can help you identify the duplicate rows:
SELECT mytablename.*
FROM dbo.mytablename
JOIN (
SELECT my_key_column
FROM dbo.mytablename
GROUP BY my_key_column
HAVING COUNT(*) > 1
) AS dups ON
dups.my_key_column = mytablename.my_key_column
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:8EC0F736-1D7B-491B-83EA-6F1CD6B64035@.microsoft.com...
> Sql Server 2005 table, a not null nvarchar column, in table design had
> made
> column a Primary Key with "Set Primary Key" menu option.
> At a later time, in table design, used "Remove Primary Key" menu option on
> the column to remove having column as a Primary Key.
> Now I need to make the column a Primary Key again. In table design right
> click menu "Set Primary Key", I click it and then Save table to save the
> change... I receive this message:
> 'mytablename (dbo)' table
> - Unable to create index 'PK_mytablename'.
> The CREATE UNIQUE INDEX statement terminated because a duplicate key was
> found for the object name 'dbo.mytablename' and the index name
> 'PK_mytablename'. The duplicate key value is (11).
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I am prevented from making the column a Primary Key. And I'm assuming this
> is because I had previously made it a Primary Key. But - I did remove it
> using the table design menu so shouldn't I be able to redo it?
> I have looked all over the indexes and constraints and ran various querys
> I've found from googliing and nothing turns up that confirms this error
> message. Meaning I can't find any constraint or index or anything else on
> the
> table and/or column.
> I can't find anything that validates the message is correct.
> How do I resolve this error and be able to successfully move past it and
> be
> able to successfully set my column as a Primary Key?
>
>|||yeah it really helps when you know exactly what an error message is really
telling you ;)
thanx, you are very correct. I had no idea about the dups. So this has been
an unexpected big help. I got myself into something now :)
____
kudos to you btw. I haven't visited an MS NG in a long while and I was not
expecting anyone to post a reply anytime soon on a Saturday afternoon (CDST
anyway ;) so I really enjoy the surprise. Even more surprising to me is that
I was surprised, because having been an MVP myself back in the day I remember
the dedication and passion behind an MVP. So shame on me for so easily
forgetting the many Saturday afternoons I spent inside the confines of the
newgroups, and the dedication of those that have the MVP Award. Thank you for
your passion on this day :)
"Dan Guzman" wrote:
> > How do I resolve this error and be able to successfully move past it and
> > be
> > able to successfully set my column as a Primary Key?
> The duplicate key error message indicates that you have more than one row in
> the table with the same proposed primary key value. You'll need to either
> delete the duplicate rows or change existing data to avoid dups. A query
> like the one below can help you identify the duplicate rows:
> SELECT mytablename.*
> FROM dbo.mytablename
> JOIN (
> SELECT my_key_column
> FROM dbo.mytablename
> GROUP BY my_key_column
> HAVING COUNT(*) > 1
> ) AS dups ON
> dups.my_key_column = mytablename.my_key_column
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David" <David@.discussions.microsoft.com> wrote in message
> news:8EC0F736-1D7B-491B-83EA-6F1CD6B64035@.microsoft.com...
> > Sql Server 2005 table, a not null nvarchar column, in table design had
> > made
> > column a Primary Key with "Set Primary Key" menu option.
> > At a later time, in table design, used "Remove Primary Key" menu option on
> > the column to remove having column as a Primary Key.
> >
> > Now I need to make the column a Primary Key again. In table design right
> > click menu "Set Primary Key", I click it and then Save table to save the
> > change... I receive this message:
> >
> > 'mytablename (dbo)' table
> > - Unable to create index 'PK_mytablename'.
> > The CREATE UNIQUE INDEX statement terminated because a duplicate key was
> > found for the object name 'dbo.mytablename' and the index name
> > 'PK_mytablename'. The duplicate key value is (11).
> > Could not create constraint. See previous errors.
> > The statement has been terminated.
> >
> >
> > I am prevented from making the column a Primary Key. And I'm assuming this
> > is because I had previously made it a Primary Key. But - I did remove it
> > using the table design menu so shouldn't I be able to redo it?
> >
> > I have looked all over the indexes and constraints and ran various querys
> > I've found from googliing and nothing turns up that confirms this error
> > message. Meaning I can't find any constraint or index or anything else on
> > the
> > table and/or column.
> > I can't find anything that validates the message is correct.
> >
> > How do I resolve this error and be able to successfully move past it and
> > be
> > able to successfully set my column as a Primary Key?
> >
> >
> >
>|||Thanks for the kind words, David. I'm spending my Saturday working on a
PowerPoint for our next local SQL User Group meeting and stop by the news
groups now and then when I need a break ;-)
--
Dan Guzman
SQL Server MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:84D3AF1A-2853-4C1B-907F-5DCCC692CF8F@.microsoft.com...
> yeah it really helps when you know exactly what an error message is really
> telling you ;)
> thanx, you are very correct. I had no idea about the dups. So this has
> been
> an unexpected big help. I got myself into something now :)
> ____
> kudos to you btw. I haven't visited an MS NG in a long while and I was not
> expecting anyone to post a reply anytime soon on a Saturday afternoon
> (CDST
> anyway ;) so I really enjoy the surprise. Even more surprising to me is
> that
> I was surprised, because having been an MVP myself back in the day I
> remember
> the dedication and passion behind an MVP. So shame on me for so easily
> forgetting the many Saturday afternoons I spent inside the confines of the
> newgroups, and the dedication of those that have the MVP Award. Thank you
> for
> your passion on this day :)
>
> "Dan Guzman" wrote:
>> > How do I resolve this error and be able to successfully move past it
>> > and
>> > be
>> > able to successfully set my column as a Primary Key?
>> The duplicate key error message indicates that you have more than one row
>> in
>> the table with the same proposed primary key value. You'll need to
>> either
>> delete the duplicate rows or change existing data to avoid dups. A query
>> like the one below can help you identify the duplicate rows:
>> SELECT mytablename.*
>> FROM dbo.mytablename
>> JOIN (
>> SELECT my_key_column
>> FROM dbo.mytablename
>> GROUP BY my_key_column
>> HAVING COUNT(*) > 1
>> ) AS dups ON
>> dups.my_key_column = mytablename.my_key_column
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:8EC0F736-1D7B-491B-83EA-6F1CD6B64035@.microsoft.com...
>> > Sql Server 2005 table, a not null nvarchar column, in table design had
>> > made
>> > column a Primary Key with "Set Primary Key" menu option.
>> > At a later time, in table design, used "Remove Primary Key" menu option
>> > on
>> > the column to remove having column as a Primary Key.
>> >
>> > Now I need to make the column a Primary Key again. In table design
>> > right
>> > click menu "Set Primary Key", I click it and then Save table to save
>> > the
>> > change... I receive this message:
>> >
>> > 'mytablename (dbo)' table
>> > - Unable to create index 'PK_mytablename'.
>> > The CREATE UNIQUE INDEX statement terminated because a duplicate key
>> > was
>> > found for the object name 'dbo.mytablename' and the index name
>> > 'PK_mytablename'. The duplicate key value is (11).
>> > Could not create constraint. See previous errors.
>> > The statement has been terminated.
>> >
>> >
>> > I am prevented from making the column a Primary Key. And I'm assuming
>> > this
>> > is because I had previously made it a Primary Key. But - I did remove
>> > it
>> > using the table design menu so shouldn't I be able to redo it?
>> >
>> > I have looked all over the indexes and constraints and ran various
>> > querys
>> > I've found from googliing and nothing turns up that confirms this error
>> > message. Meaning I can't find any constraint or index or anything else
>> > on
>> > the
>> > table and/or column.
>> > I can't find anything that validates the message is correct.
>> >
>> > How do I resolve this error and be able to successfully move past it
>> > and
>> > be
>> > able to successfully set my column as a Primary Key?
>> >
>> >
>> >