如何在 MySQL 中强制复合唯一性?
mysqlmysqli database
您可以借助 UNIQUE 关键字在 MySQL 中强制复合唯一性。以下是将 UNIQUE 关键字添加到表列的语法。
语法如下
CREATE TABLE yourTableName ( yourColumnName1 datatype, yourColumnName2 datatype, yourColumnName3 datatype, . . N UNIQUE yourConstarintName(yourColumnName2,yourColumnName3) );
为了理解上述概念，让我们创建一个包含一些列的表，并为表添加唯一约束。创建表的查询如下
mysql> create table UniqueDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100), -> StudentAge int, -> StudentMarks int -> , -> UNIQUE age_NameConstraint(StudentName,StudentAge) -> ); Query OK, 0 rows affected (0.76 sec)
您可以使用 SHOW 命令显示表中的约束名称。
语法如下
SHOW INDEX FROM yourTableName;
要显示表中的唯一约束，请使用上述语法。查询如下−
mysql> SHOW INDEX FROM UniqueDemo;
以下是输出 −
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | uniquedemo | 0 | PRIMARY | 1 | StudentId | A | 0 | NULL | NULL | | BTREE | | | YES | | uniquedemo | 0 | age_NameConstraint | 1 | StudentName | A | 0 | NULL | NULL | YES | BTREE | | | YES | | uniquedemo | 0 | age_NameConstraint | 2 | StudentAge | A | 0 | NULL | NULL | YES | BTREE | | | YES | +------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ 3 rows in set (0.33 sec)
