MySQL: Insert no duplicates for column1 and column2

Question

Create Table as follows where column1 and column2 are both foreign key values.

ID|Column1|Column2|

0 | 1 | 1

1 | 1 | 2

2 | 1 | 2

3 | 2 | 2

I don's want duplicates as with row id #2 when I insert.

I thought I could insert as so:

INSERT INTO tablename (column1, column2) VALUES (@last_id_in_col1key,@last_id_in_column2key) <*>

Then I want something like this:

<*> where column1 and column2 are not equal to @last_id_in_col1key and @last_id_in_column2key

Is there a way to add this to my table or does it have to be a seperate command?

alter table tablename add unique index(column1, column2);

Can you alter the table structure? If so, I recommend making a unique multi-column index. See this question for how to do that http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql — David Wilkins, Apr 07 '14 at 14:55
possible duplicate of [MySQL Insert Where query](http://stackoverflow.com/questions/485039/mysql-insert-where-query) — Marcus Adams, Apr 07 '14 at 14:58

score 2 · Accepted Answer · answered Apr 07 '14 at 15:07

2

It seems you're creating a so-called join table, for which the purpose is to relate items in table1 to items in table2 many to many.

This is usually done with a two-column table. The two columns in that table are both part of the primary key. You'd do this like so:

CREATE TABLE JoinTable ( 
   first_id  INT NOT NULL , 
   second_id INT NOT NULL , 

   PRIMARY KEY (first_id, second_id),

   FOREIGN KEY (first_id) 
    REFERENCES first(first_id)
    ON DELETE CASCADE,

   FOREIGN KEY (second_id) 
    REFERENCES second(second_id)
    ON DELETE CASCADE
 )

Wnen you do this, you won't be able to insert duplicate values.

answered Apr 07 '14 at 15:07

O. Jones

103,626
17
118
172

Let me try this. The problem I thought I would have is that I could not insert a value of "1" more than once in column1 if it was already inserted. – wwjdm Apr 07 '14 at 15:29
Your question had two rows with "1,2" in them. – O. Jones Apr 07 '14 at 15:30
This does not work as I want the column1 and colmn2 combination to be unique so 1|1 1|2 1|3 are unique where as 1|1 1|1 1|1| is not. – wwjdm Apr 07 '14 at 15:48
How would I add: alter table tablename add unique index(column1, column2); to your statement? – wwjdm Apr 07 '14 at 16:40

MySQL: Insert no duplicates for column1 and column2

1 Answers1