----- Original Message -----
From: "Christoph Lehmann" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, May 02, 2005 5:41 PM
Subject: newbie: delete row xy
> Hi
> I now how to use delete together with a WHERE statement. But e.g. after
> some warnings revealed me certain rows being inconsisent, and I want to
> delete them- how can I do this? I just know that I want to delete e.g
> row 1433 (I have no special id)
>
> thanks for a hint
>
In my view, every properly designed table should have a primary key. (Some
people apparently disagree with that point of view because I regularly see
table descriptions in MySQL that don't have primary keys.)
If your table has a primary key, you can use it to delete individual rows
that you don't want. For example, if the row you want to delete is for an
employee whose employee number is 1234, you can execute:
delete from employee where empno = 1234;
and be sure that one and only one row is deleted, the row of the person
whose employee number is 1234.
In my opinion, you should never be deleting a row based on its position in a
table. Therefore, if you want to delete the 1433rd row of the table, I think
you are going about your table design (and your application design) the
wrong way: you should be trying to delete a row on the basis of something
you know about it, preferably something that is unique to that row so that
you can be sure you are deleting only the row you want to delete. (If, for
example, you knew only that you wanted to delete a row of an employee named
Smith, you *could* execute 'delete from employee where lastname = 'SMITH''
but there is a very great risk that you will delete many more rows that you
intend to delete.)
Please note that my opinions are based on standard practices among database
designers that I have observed for over 20 years; I don't think you will
find my views to be eccentric or radical among database professionals.
---
The other issue raised by your question is the whole area of consistency of
your data. You are doing the right thing by removing the inconsistent data
although it would also be acceptable to correct the data so that is
consistent.
In my view, you should make a strong effort to insert only consistent data
in the first place. Several mechanisms exist within your database to help
you with this but most of them are optional and you have to choose to use
them. Therefore, I recommend that you use them!
First and foremost, I strongly believe that you should practice good
database design, including selection of primary keys for every table and
normalization to at least Third Normal Form (with denormalization as
appropriate).
You should also take advantage of Referential Integrity. This will ensure
that your primary and foreign keys remain consistent. It makes no sense to
store the value of 'XYZ' as a department code for an employee if there is no
department XYZ!
Column and table constraints, if available, should also be used to ensure
that the data put into columns is valid. For instance, if you know that the
only valid values for the Sex column of your employee table are 'M' and 'F',
then enforce that as a condition. If your database lets you build those
rules into the table definition itself, so much the better but if your
database does not permit this, be sure that you write these rules into the
programs that create and change your data.
Rhino
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
