How to implement CANDIDATE key in any MySQL table?

Each relation may have one or more candidate key. One of these candidate keys is called Primary Key. Each candidate key qualifies for Primary Key. Therefore, candidates for Primary Key is called Candidate Key. To implement candidate key in MySQL, set more than one column as unique key. These keys would qualify for candidate key as in the below syntax −

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

Let us first create a −

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)

Following is the query to implement CANDIDATE key 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

Insert some records in the table using insert −

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)

Display all records from the table using select −

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)

AmitDiwan

Updated on 11-Nov-2019 10:47:16

• Related Questions & Answers
• Candidate Key in RDBMS
• How to identify composite primary key in any MySQL database table?
• Difference between Primary Key and Candidate key
• Difference between Super Key and Candidate key
• Difference Between Primary and Candidate Key
• How to make MySQL table primary key auto increment?
• How to get primary key of a table in MySQL?
• Remove unique key from MySQL table?
• How to reset the primary key of a table in mysql?
• Creating Unique Key in MySQL table referring to date?
• How to refer primary key as Foreign to various table in MySQL?
• Implement INSERT … ON DUPLICATE KEY UPDATE in MySQL
• ALTER TABLE to add a composite primary key in MySQL?
• Implement GREATEST() in MySQL and update the table?
• Create a table in MySQL and implement TIMESTAMPDIFF()?