I can't work out. Duplicate row

Dear all,
I am not be able to delete a row in the following table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblSysRights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSysRights]
GO
CREATE TABLE [dbo].[tblSysRights] (
[sinRightID] [smallint] IDENTITY (1, 1) NOT NULL ,
[sinUserID] [smallint] NOT NULL ,
[sinApplicationID] [smallint] NOT NULL ,
[sinType] [smallint] NULL
) ON [PRIMARY]
GO
No indexes. No constraints. No relationships. What on earth hell is happenin
g?
SELECT *
FROM tblSysRights
WHERE (sinUserID = 61) AND (sinApplicationID = 163)
725 61 163 null
725 61 163 null
I obtain a warning which tell me:
"key column information is insufficient. too many rows affected"
Thanks in advance and best regards,Set RowCount 1
Delete
FROM tblSysRights
WHERE (sinUserID = 61) AND (sinApplicationID = 163)
Set RowCount 0
Madhivanan|||Enric,

> I am not be able to delete a row in the following table:
Can you be more specific?
From where and how are you trying delete the row?
AMB
"Enric" wrote:

> Dear all,
> I am not be able to delete a row in the following table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblSysRights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblSysRights]
> GO
> CREATE TABLE [dbo].[tblSysRights] (
> [sinRightID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [sinUserID] [smallint] NOT NULL ,
> [sinApplicationID] [smallint] NOT NULL ,
> [sinType] [smallint] NULL
> ) ON [PRIMARY]
> GO
> No indexes. No constraints. No relationships. What on earth hell is happen
ing?
> SELECT *
> FROM tblSysRights
> WHERE (sinUserID = 61) AND (sinApplicationID = 163)
> 725 61 163 null
> 725 61 163 null
> I obtain a warning which tell me:
> "key column information is insufficient. too many rows affected"
> Thanks in advance and best regards,
>
>|||Thanks for that Madhinavan.
Done successfully.
Thanks Alejandro, too.
Regards,
"Alejandro Mesa" wrote:
> Enric,
>
> Can you be more specific?
> From where and how are you trying delete the row?
>
> AMB
>
> "Enric" wrote:
>|||Just to add a couple of points. For elimination of duplicates, refer to:
http://support.microsoft.com/defaul...b;EN-US;q139444
Use keys in all your tables, and you will avoid a lot of such headaches. On
a related note, a couple of comments:
* You seem to have an identity column which is constrained to be unique, but
apparently it seems to be violated with SET IDENTITY_INSERT ON setting.
Check where the "hole" is.
* Your naming conventions are stale which can be better -- search for some
good guidelines in the archives of this newsgroup.
* Use Query Analyzer for testing your queries, the EM query interface is
buggy and has certain limitations.
* And most importantly, use primary keys in your tables. In relational
database, a value is identified using <table, column, key> triplet. With
duplicates, such identification becomes impossible and relational operations
cannot be used beneficially. The impossibility of logically deleting one row
out of duplicates is often cited as a prime illustration of the consequence
of allowing duplicates by SQL. ( SQL language inventors and its flag bearers
should take the credit, for even standardizing them ! )
Anith|||Thanks for that Anith.
That table was defined without identity fields or key. Just a mess-up.
Best Regards,
"Anith Sen" wrote:

> Just to add a couple of points. For elimination of duplicates, refer to:
> http://support.microsoft.com/defaul...b;EN-US;q139444
> Use keys in all your tables, and you will avoid a lot of such headaches. O
n
> a related note, a couple of comments:
> * You seem to have an identity column which is constrained to be unique, b
ut
> apparently it seems to be violated with SET IDENTITY_INSERT ON setting.
> Check where the "hole" is.
> * Your naming conventions are stale which can be better -- search for some
> good guidelines in the archives of this newsgroup.
> * Use Query Analyzer for testing your queries, the EM query interface is
> buggy and has certain limitations.
> * And most importantly, use primary keys in your tables. In relational
> database, a value is identified using <table, column, key> triplet. With
> duplicates, such identification becomes impossible and relational operatio
ns
> cannot be used beneficially. The impossibility of logically deleting one r
ow
> out of duplicates is often cited as a prime illustration of the consequenc
e
> of allowing duplicates by SQL. ( SQL language inventors and its flag beare
rs
> should take the credit, for even standardizing them ! )
> --
> Anith
>
>

i cant delete a row

i cant delete a row on my table. when i click delete it say : "Insufficient key column information for updating or refreshing....." what can i do?The message smells like you might be using Microsoft Access to edit a table stored in Microsoft SQL, but that you don't have all of the defined key columns in the edit control. If that is correct:

1. Verify that there is a defined key
1a. Should be a PRIMARY KEY definition
1b. Could be a UNIQUE constraint
1c. Might be a UNIQUE INDEX
2. Identify all columns in all of the potential defined key(s) from step 1.
3. Ensure that all of the columns from at least one of the defined keys are present in your edit control.

-PatP|||thanks but i didnt use access. i copy and paste a row in my table. then i wanted to delete that row but i cant delete. all of my other rows can be cleaned but that row cant be delete.|||Nope, it isn't possible to "copy and paste a row in my table" because there isn't any GUI representation for a table. What you are doing (very possibly without realizing it) is copying and pasting within some application running on your machine, not actually on the table itself.

Since you haven't given us (the readers) many clues, but you have ruled out Microsoft Access, I'll make another guess... This time I'll guess Captain Peacock, in the Drawing Room... Oh wait, wrong game! ;)

Let's try this guess: Are you possibly using SQL Enterprise Manager, and Editing a table grid control? If so, please let me know and we can start to solve your problem... If not, I might try to guess again!

Just in case you didn't get the idea, I'm frustrated. You're asking me to help you solve a problem, but you haven't given me anything concrete to work from. I assume that you are using SQL Server, and that you haven't used it very much so you're handicapped in explaining your problem, and it appears that English isn't your native language which would further complicate things for you... I'm trying to help, but I need you to help me more so that I can help you!

If you can read English comfortably, please see this forum's FAQ for Brett Kaiser's excellent suggestions for How to Ask a Question to Get Quick and Correct Answers (http://www.dbforums.com/showthread.php?t=1212452#post4527530).

-PatP|||yes! you are right. im sorry. now i am using sql server 2000 enterprise manager. i create a table (same nortwind "order details"). remember i create an olap cube such as nwind
here is my fields:

accountno (varchar50)
medicineno(nvarchar15)
saleprice(float8)
quantity(float8)

i click return all rows and enter my records

1 101 $4 3
1 102 $3 2
2 102 $5 3
3 207 $2 1
....................
56 101 $4 2

and then i copy this row (2 102 $3 2) and paste it

........................
56 101 $4 2
2 102 $3 2

then i wanted to delete that row (2 102 $3 2)
but it cant be deleted. warning: "key column info insufficient or wrong. updating affects more rows" (i translated english. maybe it can be wrong)|||Have dealt with this in the past dealing with others issues.

You need to write a delete statment to accomplish this in query analyzer.

delete from table where accountno = 2

try inserting a new row as follows|||The problem stems from the fact that you do not have a unique primary key defined on your table. Thus, when you try to delete one record SQL Server cannot tell which record it is.
Add a primary key to your table. A surrogate key will do fine. But I also agree that you should not edit data from the Enterprise Manager GUI. Use Query Analyzer.|||thanks i will try