How to set two column unique in SQL
I am creating a table ,in the table two开发者_如何学C column is unique, I mean columnA and columnB do not have same value: such as :
Table X
A B
1 2(RIGHT,unique)
2 2(RIGHT, unique)
1 3(RIGHT, not unique)
2 3(RIGHT, not unique)
1 2 (WRONG, not unique)
How to create such a table? many thanks!
create table X
(
[ID] INTEGER PRIMARY KEY AUTOINCREASE NOT NULL,\
[A] INTEGER,
[B] INTEGER);
Create a unique key column:
CREATE TABLE X
(
ID INTEGER PRIMARY KEY AUTOINCREASE NOT NULL,
A INTEGER,
B INTEGER,
UNIQUE KEY(A, B)
);
INSERT INTO X(A, B) VALUES(1, 2);
INSERT INTO X(A, B) VALUES(2, 2);
INSERT INTO X(A, B) VALUES(1, 3);
INSERT INTO X(A, B) VALUES(2, 3);
INSERT INTO X(A, B) VALUES(1, 2);
The last line will fail because the combination a = 1 and b = 2 already exists in the table.
CREATE UNIQUE INDEX `my_index_name` ON `my_table` (`col1`,`col2`)
精彩评论