update with no primary key

[pneb]

I have a couple of tables with no unique columns (it's a list of awards –sometimes one person receives many awards or the same award is given to diffferent people). Is their any way to update without having a primary key/ And why set it up this way? PHPMyAdmin allows updates without a primary key.

Thank you,

Patty

[Shadow]

Not just phpMyAdmin, virtually every other MySql client allows it…

You should create 3 tables insted of the one you have curently:

– one to hold the data for awards,

– another to store the data of the persons,

– a third one holding the primary keys of the previously mentioned tables.

[CalEvans]

Quote:

Is their any way to update without having a primary key/ And why set it up this way?

A qualified no.

#1 You should never create a table without a primary key.

You MAY be able to update and build a where clause that includes enough of your fields so that the resulting update only updates 1 record.

Code:

Update myBrokenTable set name='Cal' where address='myaddress' AND city='myCity' AND zip='myZip' AND phone='myPhone';

This may work but unless you've got one of those fields set to unique then there is no guarantee that the same address won't be in your table more than once. (In that case all, records would be updated.)

Quote:

PHPMyAdmin allows updates without a primary key.

And so does Access but you don't see me running to use it either. Just because a client (phpMyAdmin is a client) will allow you to do something does not mean that it's a good thing to do. I'm not sure how phpMyAdmin accomplishes this feat of magic because SQL by it's very nature does not have record or row numbers. Therefore to issue an update you have to construct an UPDATE statement.

A PK using MySQL's auto_increment feature would solve this problem for you in a painless way.

Sorry that's not the answer you were probably looking for but it is the correct answer.

HTH,

=C=

[Author]

Posts