douyong2531
2015-02-06 18:28 阅读 51
已采纳

Sql使用重复键插入

Ok so, im still a beginner in databases. i have this code

$sql="INSERT INTO complaints_members(password, complaint) VALUES ('$mypassword','$submit') ON DUPLICATE KEY UPDATE complaint='$submit' ; ";

This simply updates my complaint in the existing entry. How can i insert a new entry with the same key, instead of updating the old one?

Im thinking of it like this.

1st entry is like

Password : 123

Complaint : abc

2nd would be like

Password : 123

Complaint : def

Im not very familiar with the terms of SQL, so i'm sorry in advance, and thanks for your time.

EDIT: This is how the tables are

enter image description here

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    duanqi6274 duanqi6274 2015-02-06 18:34

    You can't have duplicate primary keys in a database. This is intentional.

    Instead, consider re-designing your database so that each complaint has a unique ID (AKA a Primary Key). You should set it as something like complaint_id or simply id, and make it a PK (Primary Key) and AI (Auto-Increment). That way, when you do inserts you won't have to worry about updating the same row.

    点赞 评论 复制链接分享
  • doushang4293 doushang4293 2015-02-06 18:42

    One option is to make password and complaint a composite primary key

    点赞 评论 复制链接分享
  • douji7399 douji7399 2015-02-06 19:13

    For future reference when someone asks for your table structure its better to post the text from SHOW CREATE TABLE table_name instead of an image from a visual editor.

    That said the problem is that your primary key is the password field. You need to add primary keys to both tabled than can be uniquely identified and then you need to link them.

    Your table structure should be more like this:

    CREATE TABLE `register` (
       `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
       `username` varchar(255) NOT NULL,
       `password` varchar(255) NOT NULL,
       `email` varchar(255) NOT NULL,
       `number` INTEGER(255),
       `address` varchar(255),
       PRIMARY KEY (`id`),
       -- I assume that email and username should always be unique here
       UNIQUE KEY (`email`)
       UNIQUE KEY (`username`)
    );
    
    CREATE TABLE `complaints_members`
       `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
       `complaint` VARCHAR(255),
       `password` varchar(255),
       `member_id` INTEGER UNSIGNED NOT NULL,
       PRIMARY KEY (`id`),
       KEY (`member_id`),
       CONSTRAINT `complaints_members_register` FOREIGN KEY (`member_id`) REFERENCES `register` (`id`) ON DELETE CASCADE
    );
    

    So now to create a new member complaint your SQL would look like

    INSERT INTO complaints_members(member_id, password, complaint) VALUES (?, ?, ?)

    And to get all complaints for a member:

    SELECT c.* 
    FROM compalaints_members c
    WHERE c.member_id = ?
    
    点赞 评论 复制链接分享

相关推荐