How to implement CANDIDATE key in any MySQL table?

Each relationship may have one or more candidate keys. One of these candidate keys is called the primary key. Every candidate key is eligible to become a primary key. Therefore, candidate primary keys are called candidate keys. To implement candidate keys in MySQL, set more than one column as a unique key. These keys will qualify as candidate keys as shown in the following syntax -

alter table yourTableName add unique key anyName(yourColumnName1,yourColumnName2);

Let us first create one -

mysql> create table DemoTable1400
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> Name varchar(40),
   -> Age int
   -> );
Query OK, 0 rows affected (0.50 sec)

Here is the query to implement candidate keys in any MySQL:

mysql> alter table DemoTable1400 add unique key name_age_unique(Name,Age);
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

Use insert to insert some records in the table -

mysql> insert into DemoTable1400(Name,Age) values('Chris',23);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1400(Name,Age) values('Chris',23);
ERROR 1062 (23000): Duplicate entry 'Chris-23' for key 'name_age_unique'
mysql> insert into DemoTable1400(Name,Age) values('David',23);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1400(Name,Age) values('Chris',24);
Query OK, 1 row affected (0.10 sec)

Use select − to display all records in the table

mysql> select * from DemoTable1400;

This will produce the following output-

+----+-------+------+
| Id | Name  | Age  |
+----+-------+------+
|  1 | Chris |   23 |
|  4 | Chris |   24 |
|  3 | David |   23 |
+----+-------+------+
3 rows in set (0.00 sec)

The above is the detailed content of How to implement CANDIDATE key in any MySQL table?. For more information, please follow other related articles on the PHP Chinese website!