如何使一对列在MySQL中唯一？

要使一对列唯一，请使用带有ALTERTABLE命令的UNIQUE。以下是语法-

alter table yourTableName add unique yourUniqueName(yourColumnName1,yourColumnName2,...N);

让我们首先创建一个表-

create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(100), StudentLastName varchar(100), StudentAge int, StudentPhoneNumber varchar(20) );

以下是在MySQL中创建一对唯一列的查询-

alter table DemoTable add unique DemoTable_unique_StudentFirstName_StudentPhoneNumber(StudentFirstName,StudentPhoneNumber); Records: 0 Duplicates: 0 Warnings: 0

使用插入命令在表中插入一些记录-

insert into DemoTable(StudentFirstName,StudentLastName,StudentAge,StudentPhoneNumber) values(John,Doe,21,9878567878); insert into DemoTable(StudentFirstName,StudentLastName,StudentAge,StudentPhoneNumber) values(John,Smith,23,7654674545); insert into DemoTable(StudentFirstName,StudentLastName,StudentAge,StudentPhoneNumber) values(John,Brown,21,9878567878); ERROR 1062 (23000): Duplicate entry John-9878567878 for key DemoTable_unique_StudentFirstName_StudentPhoneNumber

使用select语句显示表中的所有记录-

select *from DemoTable;

这将产生以下输出-

+-----------+------------------+-----------------+------------+--------------------+ | StudentId | StudentFirstName | StudentLastName | StudentAge | StudentPhoneNumber | +-----------+------------------+-----------------+------------+--------------------+ | 1 | John | Doe | 21 | 9878567878 | | 2 | John | Smith | 23 | 7654674545 | +-----------+------------------+-----------------+------------+--------------------+ 2 rows in set (0.00 sec)