MySQL:在column1和column2中插入no副本。

时间:2021-12-07 23:19:49

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

创建表,如下所示:column1和column2都是外键值。

ID|Column1|Column2|

ID | Column1 | Column2 |

0 | 1 | 1

0 | 1 | 1。

1 | 1 | 2

1 | 1 | 2。

2 | 1 | 2

| 1 | 2。

3 | 2 | 2

| 2 | 2。

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

当插入的时候,我想要复制和行id #2一样。

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?

是否有办法将其添加到我的表中,或者它必须是一个seperate命令?

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

1 个解决方案

#1


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.

看起来您正在创建一个所谓的连接表,其目的是将table1中的项目与表2中的项目关联起来。

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.

如果你这样做,你就不能插入重复的值。

#1


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.

看起来您正在创建一个所谓的连接表,其目的是将table1中的项目与表2中的项目关联起来。

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.

如果你这样做,你就不能插入重复的值。