Delete row not from primary key

[grilldor]

Hum why cant we delete a row by searching for a a value in a specific collumn? do we absolutely need a primary key?

[jsladek]

No you don't absolutely need a Primary key [b]but you should have one[/b]. If you don't have one you can do a delete matching all fields for the record you are trying to delete and putting a LIMIT 1 on the end unless your trying to delete multiple records from the data base. 

Once again, you should have a PK. Even if it is an auto incrementing key.

Regards,
John Sladek

[fenway]

Funny you should mention that... in fact, that's a "bug" in the old MySQL control center, and maybe MySQL administrator as well: you can't edit a record in a table without a PK, because it has no way of knowing which record you're actually playing with.

[jsladek]

Well, I'm working on a program and I was wondering how I can edit or delete a record in a table that did not have a primary key.  I checked phpMyAdmin and it looked to me that they were just referencing all the feilds (or at least that is what I thought) so I decided to do the same thing. I figured if the table does not have a primary key if my where clause includes ALL the feilds and values for them and I use LIMIT 1 then I would change at least one record that is exactly like the one I am looking at.  I figured the end result would still be what was intended.

-John Sladek

[fenway]

Well, yes, it should be... but in the event that there is another matching row, you might get a random 1 with your limit clause.