[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Primary key

micatran

3/29/2007 9:35:00 PM

Hi,

I am using sql server, and I want to add a primary key but I get this
error.

'1_REFAREA_ZIP_95' table
- Unable to create index 'PK_1_REFAREA_ZIP_95'.
ADO error: CREATE UNIQUE INDEX terminated because a duplicate key was
found for index ID 1. Most significant primary key is '37892'.
Could not create constraint. See previous errors.
The statement has been terminated.


I know that I have a duplicate ID, but how do I delete the ID if I
dont have a unique key or primary key specified. It says the database
is read-only, and I cannot delete the record. Please help.

Thank you in advance.
4 Answers

Tom Moreau

3/29/2007 9:40:00 PM

0

Well, if the DB is read-only, you can't create any constraints - even if
none of your data violate the constraints.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
<micatran@gmail.com> wrote in message
news:1175204095.352626.163510@e65g2000hsc.googlegroups.com...
Hi,

I am using sql server, and I want to add a primary key but I get this
error.

'1_REFAREA_ZIP_95' table
- Unable to create index 'PK_1_REFAREA_ZIP_95'.
ADO error: CREATE UNIQUE INDEX terminated because a duplicate key was
found for index ID 1. Most significant primary key is '37892'.
Could not create constraint. See previous errors.
The statement has been terminated.


I know that I have a duplicate ID, but how do I delete the ID if I
dont have a unique key or primary key specified. It says the database
is read-only, and I cannot delete the record. Please help.

Thank you in advance.

micatran

3/29/2007 9:49:00 PM

0

Sorry for my mistake, it is not read-only. It will not let me change
records because there is no unique key. Correct me if I am wrong.


On Mar 29, 5:39 pm, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:
> Well, if the DB is read-only, you can't create any constraints - even if
> none of your data violate the constraints.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .<micat...@gmail.com> wrote in message
>
> news:1175204095.352626.163510@e65g2000hsc.googlegroups.com...
> Hi,
>
> I am using sql server, and I want to add a primary key but I get this
> error.
>
> '1_REFAREA_ZIP_95' table
> - Unable to create index 'PK_1_REFAREA_ZIP_95'.
> ADO error: CREATE UNIQUE INDEX terminated because a duplicate key was
> found for index ID 1. Most significant primary key is '37892'.
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I know that I have a duplicate ID, but how do I delete the ID if I
> dont have a unique key or primary key specified. It says the database
> is read-only, and I cannot delete the record. Please help.
>
> Thank you in advance.

David Portas

3/29/2007 9:50:00 PM

0

On 29 Mar, 22:34, micat...@gmail.com wrote:
> Hi,
>
> I am using sql server, and I want to add a primary key but I get this
> error.
>
> '1_REFAREA_ZIP_95' table
> - Unable to create index 'PK_1_REFAREA_ZIP_95'.
> ADO error: CREATE UNIQUE INDEX terminated because a duplicate key was
> found for index ID 1. Most significant primary key is '37892'.
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I know that I have a duplicate ID, but how do I delete the ID if I
> dont have a unique key or primary key specified. It says the database
> is read-only, and I cannot delete the record. Please help.
>
> Thank you in advance.

First take a look at the duplicate rows and decide which you want to
delete:

SELECT * FROM [1_REFAREA_ZIP_95] WHERE id = 37892;

Then:

DELETE FROM [1_REFAREA_ZIP_95]
WHERE id = 37892 AND ... ? /* you specify... */

If there are lots of duplicates and/or there are some totally
identical rows then you may want to take more drastic measures. DON'T
try the following unless you have a backup copy of the data:

WITH t AS
(SELECT id, x, y, z,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY x,y,z) AS r
FROM [1_REFAREA_ZIP_95])
DELETE FROM t
WHERE r>1;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

micatran

3/29/2007 9:52:00 PM

0

Thank you very much sir.


On Mar 29, 5:50 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> On 29 Mar, 22:34, micat...@gmail.com wrote:
>
>
>
>
>
> > Hi,
>
> > I am using sql server, and I want to add a primary key but I get this
> > error.
>
> > '1_REFAREA_ZIP_95' table
> > - Unable to create index 'PK_1_REFAREA_ZIP_95'.
> > ADO error: CREATE UNIQUE INDEX terminated because a duplicate key was
> > found for index ID 1. Most significant primary key is '37892'.
> > Could not create constraint. See previous errors.
> > The statement has been terminated.
>
> > I know that I have a duplicate ID, but how do I delete the ID if I
> > dont have a unique key or primary key specified. It says the database
> > is read-only, and I cannot delete the record. Please help.
>
> > Thank you in advance.
>
> First take a look at the duplicate rows and decide which you want to
> delete:
>
> SELECT * FROM [1_REFAREA_ZIP_95] WHERE id = 37892;
>
> Then:
>
> DELETE FROM [1_REFAREA_ZIP_95]
> WHERE id = 37892 AND ... ? /* you specify... */
>
> If there are lots of duplicates and/or there are some totally
> identical rows then you may want to take more drastic measures. DON'T
> try the following unless you have a backup copy of the data:
>
> WITH t AS
> (SELECT id, x, y, z,
> ROW_NUMBER() OVER (PARTITION BY id ORDER BY x,y,z) AS r
> FROM [1_REFAREA_ZIP_95])
> DELETE FROM t
> WHERE r>1;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
> --- Hide quoted text -
>
> - Show quoted text -