CODEWITHSUNDEEP
mysqlsql
partiz
partiz

Reputation: 1216

create table and coding to avoid duplicate new row in mysql

id  name    title   city
1    n        t     c
2    n1       t1    c1
3    n3       t3    c2

I want avoid insert dublicate on insert.(I want ignore insert new row on dublicate:

first I create a table:

CREATE TABLE IF NOT EXISTS Form (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,   
            name VARCHAR(243) COLLATE utf8_unicode_ci NOT NULL,
            title VARCHAR(243) COLLATE utf8_unicode_ci NOT NULL,
            city VARCHAR(243) COLLATE utf8_unicode_ci NOT NULL,
            UNIQUE (name),
            UNIQUE (title)      
        ) DEFAULT COLLATE utf8_unicode_ci;

My structure table is true?

Now in sql code:

INSERT INTO Form (name,title,city) VALUES ('name'),('title'),('city') ON DUPLICATE KEY UPDATE name=name AND title = title;

is My source code correct (syntax & coding) ? or need some more? for example do I need add unique in table?

Upvotes: 0

Views: 50

Answers (1)

kojow7
kojow7

Reputation: 11384

As you have it now, with UNIQUE (name), UNIQUE (title), this will allow for: 

id  name    title   city
1    n1       t1     c
2    n2       t2     c
3    n3       t3     c

but you can also have a combined UNIQUE key so that you can have the same names and same titles, just not when they are combined. So UNIQUE (name, title) will allow you to have:

id  name    title   city
1    n1       t1     c
2    n1       t2     c
3    n3       t2     c

Notice you have some names the same and some titles the same, but a combination of both name and title can never be the same. Also, the above two code examples will allow for duplication of city. If you also want city to be unique, add it to the above examples.

Also, your code:

INSERT INTO Form (name,title,city) VALUES ('name'),('title'),('city') ON DUPLICATE KEY UPDATE name=name AND title = title;

will not work. Your unique values will take care of not inserting the duplicate row, so your insert code should just look like this:

INSERT INTO Form (name,title,city) VALUES ('Fred', 'Mr.', 'Bedrock');

Upvotes: 1

Related Questions