Can't delete rows from a table in SQL Server 2000 without a primary key 
Author Message
 Can't delete rows from a table in SQL Server 2000 without a primary key

I know this is not pertinent to this newsgroup, but a lot of people here use
SQL Server 2000, so maybe someone can help. Besides, I'm using it from a C#
program ;-)

I have a table where I know there will be duplicated rows, so I didn't
define a primary key. I can insert values and all seems to work, but when I
open the table from the Enterprise Manager and I try to delete a row, I get
an error messages saying "insufficient or wrong informations on key column,
too many rows updated" or something similar (I'm using the Italian version
of SQL Server, the original error message is "Informazioni sulla colonna
chiave insufficienti o errate. Troppe righe interessate
dall'aggiornamento").
This strange behaviour disappears if I create a column of type UID and set
it as the primary key. But I can't do this, it will enlarge that table
really too much!

Can anyone help on this ?

Massimo



Sat, 18 Jun 2005 08:12:45 GMT  
 Can't delete rows from a table in SQL Server 2000 without a primary key
Massimo, Enterprise manager is trying to save you from deleting rows which
you don't want to delete.  It doesn't have any way of determining which rows
you want to delete and which you don't.  Behind the scenes it is just using
SQL commands to execute whatever you do with the UI.

Is there anything about the rows which you want to delete which
differentiates them from the rows you do not?  What I would do is add a
column to your table which is TableID.  This TableID will not be used by
your app  but will enable you to differentiate between these duplicate rows
in whatever SQL you are using to update/delete them.  Also, adding one more
column which is a unique int to your table won't affect it's size to any
noticeable degree.

--
Greg Ewing [MVP]
http://www.claritycon.com


Quote:
> I know this is not pertinent to this newsgroup, but a lot of people here
use
> SQL Server 2000, so maybe someone can help. Besides, I'm using it from a
C#
> program ;-)

> I have a table where I know there will be duplicated rows, so I didn't
> define a primary key. I can insert values and all seems to work, but when
I
> open the table from the Enterprise Manager and I try to delete a row, I
get
> an error messages saying "insufficient or wrong informations on key
column,
> too many rows updated" or something similar (I'm using the Italian version
> of SQL Server, the original error message is "Informazioni sulla colonna
> chiave insufficienti o errate. Troppe righe interessate
> dall'aggiornamento").
> This strange behaviour disappears if I create a column of type UID and set
> it as the primary key. But I can't do this, it will enlarge that table
> really too much!

> Can anyone help on this ?

> Massimo



Sat, 18 Jun 2005 14:12:29 GMT  
 Can't delete rows from a table in SQL Server 2000 without a primary key


Quote:
> Massimo, Enterprise manager is trying to save you from deleting rows which
> you don't want to delete.  It doesn't have any way of determining which
rows
> you want to delete and which you don't.  Behind the scenes it is just
using
> SQL commands to execute whatever you do with the UI.

Yes, I understand this, but what is the sense in letting the user create a
table without a primary key if he can't use it ?!?

Quote:
> Is there anything about the rows which you want to delete which
> differentiates them from the rows you do not?  What I would do is add a
> column to your table which is TableID.  This TableID will not be used by
> your app  but will enable you to differentiate between these duplicate
rows
> in whatever SQL you are using to update/delete them.  Also, adding one
more
> column which is a unique int to your table won't affect it's size to any
> noticeable degree.

This is unfortunately not true, since the existing columns are quite small
(two smallints and a datetime). Anyway, how can I get unique ints without
using a UID type (which is quite big) ?

Massimo



Sat, 18 Jun 2005 20:28:11 GMT  
 Can't delete rows from a table in SQL Server 2000 without a primary key
You can use the table. You just can't delete a single specific row if it is
a duplicate. You either have to delete all duplicate rows, or none.

e.g.

1, 'a'
1, 'b'
1, 'b'
2, 'a'
2, 'b'

It isn't possible to only delete one of the 1, 'b' rows. You have to delete
both of them.

In general it's a bad idea to allow duplicate rows in a table, especially
without some way of distinguishing them.

Colin


Quote:

messaggio

> > Massimo, Enterprise manager is trying to save you from deleting rows
which
> > you don't want to delete.  It doesn't have any way of determining which
> rows
> > you want to delete and which you don't.  Behind the scenes it is just
> using
> > SQL commands to execute whatever you do with the UI.

> Yes, I understand this, but what is the sense in letting the user create a
> table without a primary key if he can't use it ?!?



Sat, 18 Jun 2005 22:17:15 GMT  
 Can't delete rows from a table in SQL Server 2000 without a primary key
Massimo,

inline


Quote:

> messaggio

> > Massimo, Enterprise manager is trying to save you from deleting rows
> > which
> > you don't want to delete.  It doesn't have any way of determining which
> rows
> > you want to delete and which you don't.  Behind the scenes it is just
> using
> > SQL commands to execute whatever you do with the UI.

> Yes, I understand this, but what is the sense in letting the user create a
> table without a primary key if he can't use it ?!?

So that you can delete a single row among duplicates or more generally so
that you can easily distinguish an individual row.  As Colin pointed out,
it's generally not a good idea to allow duplicate rows which have no
distinguishing factors.

Quote:

> > Is there anything about the rows which you want to delete which
> > differentiates them from the rows you do not?  What I would do is add a
> > column to your table which is TableID.  This TableID will not be used by
> > your app  but will enable you to differentiate between these duplicate
> rows
> > in whatever SQL you are using to update/delete them.  Also, adding one
> more
> > column which is a unique int to your table won't affect it's size to any
> > noticeable degree.

> This is unfortunately not true, since the existing columns are quite small
> (two smallints and a datetime).

True, as a percentage of a column those 4 bytes are a large (2 bytes for
each of the smallints and 8 bytes for the datetime) but will these 4 bytes
really affect things?  That depends on your hardware and requirements.

Quote:
>Anyway, how can I get unique ints without
> using a UID type (which is quite big) ?

Set the column to be an int and an Identity column wiht the default seed of
1.  Then SQL server will increment this whenever you add a row.

Here is a link to MS's online DB design resource center.  There is lots of
good info there on DB design which will probably be helpful to you.
http://support.microsoft.com/default.aspx?scid=/support/access/conten...

--
Greg Ewing [MVP]
http://www.claritycon.com/

Quote:

> Massimo



Sat, 18 Jun 2005 22:46:15 GMT  
 Can't delete rows from a table in SQL Server 2000 without a primary key


Quote:
> > > Massimo, Enterprise manager is trying to save you from deleting rows
> > > which you don't want to delete.  It doesn't have any way of
determining
> > > which rows you want to delete and which you don't.  Behind the scenes
> > > it is just using SQL commands to execute whatever you do with the UI.

> > Yes, I understand this, but what is the sense in letting the user create
a
> > table without a primary key if he can't use it ?!?

> So that you can delete a single row among duplicates or more generally so
> that you can easily distinguish an individual row.  As Colin pointed out,
> it's generally not a good idea to allow duplicate rows which have no
> distinguishing factors.

Yes, I know this, but I'm putting some program logs into it, in the form

log_type log_time log_text

Unfortunately, Sql Server doesn't index "text" columns, so I need to use the
others as a primary key... and they can actually be duplicated.

Quote:
> >Anyway, how can I get unique ints without
> > using a UID type (which is quite big) ?

> Set the column to be an int and an Identity column wiht the default seed
of
> 1.  Then SQL server will increment this whenever you add a row.

Thanks.

Massim



Sun, 19 Jun 2005 02:42:01 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. NEED: PDC 2000 Presentation on implementing a hierarchical filesystem using SQL Server tables

2. Multiple Table Return in SQL Server 2000

3. Create table in SQL Server 2000

4. Can't write to SQL Server table

5. Pocket PC access to SQL Server 2000

6. Access SQL Server 2000 Data Analysis Services

7. SQL Server 2000 Trial Version ?

8. SQl Server 2000

9. SQL 2000 Server Stuff - 33 CDs

10. SqlCommand object and stored procedures in SQL Server 2000

11. SQL server 2000 , a bug related to ODBC DSN ADO related

12. Executing stored procedure on sql server 2000 using c program

 

 
Powered by phpBB® Forum Software