dongyi9484
dongyi9484
2013-03-18 19:18

重复密钥更新..数据库密钥?

已采纳

I have a table (id, name, price, menu_id)

I have a page in which every item's name and price is shown, and the users can edit it.

But when I submit, it creates new rows.

The index is on the primary key. Should it be on something else?

I tried the following:

INSERT INTO gerechten 
SET gerecht_naam = :naam, gerecht_prijs = :prijs, menu_id = :menuid 
ON DUPLICATE KEY 
UPDATE gerecht_naam = :naam, gerecht_prijs = :prijs
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongzhan2029 dongzhan2029 8年前

    your query will transform to an update whenever any unique key crashes in your table. Up to you to decide where this key should be placed on. For example, your query could be:

    INSERT INTO `gerechten` (`name`, `price`, `menu_id`) 
    VALUES  (:name, :price, :menuid)
    ON DUPLICATE KEY UPDATE 
        name = :name, 
        price = :price
    

    with a unique index on name AND menu_id for example. (I believe there can be several times the same name as long as it is in a different menu)

    Of course if there are various fields combinations that must be unique this is a problematic situation and maybe you should use a different approach, such as a select ... lock statement to check previous existence of the values being edited.

    点赞 评论 复制链接分享
  • drbii0359 drbii0359 8年前

    You need to add a UNIQUE key to the gerech_naam column and/or to the gerech_prijs (you can have two columns define uniqueness of a row).

    If you were to implement these unique keys to the table, you have to be aware that you would still have a problem because if you change the name or the price it could possibly insert in the DB because the data you provide doesn't exist in the table.

    点赞 评论 复制链接分享

相关推荐