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

Published on 11-Nov-2019 10:46:45

Previous Page Print Page