We can apply the PRIMARY KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement. SyntaxALTER TABLE table_name MODIFY colum_name datatype PRIMARY KEY;                  OR ALTER TABLE table_name ADD PRIMARY KEY (colum_name); Suppose we have the following table named ‘Player’ and we want to add the PRIMARY KEY constraint to the column ‘ID’ then it can be done with the help of ALTER TABLE command as follows −mysql> DESCRIBE Player; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | ... Read More

As we know that a PRIMARY KEY column must have unique values and cannot have null values hence if we will define a column with UNIQUE and NOT NULL constraint both then that column would become PRIMARY KEY column.ExampleIn this example, we have created a table ‘Student123’ by defining column ‘RollNo’ with UNIQUE and NOT NULL constraints. Now, by describing the table we can see that ‘RollNo’ is the PRIMARY KEY column.mysql> Create table Student123(RollNo INT UNIQUE NOT NULL, Name varchar(20)); Query OK, 0 rows affected (0.25 sec) mysql> DESCRIBE Student123; +--------+-------------+------+-----+---------+-------+ | Field  | Type     ... Read More

PRIMARY KEY uniquely identifies each row in a database. A PRIMARY KEY must contain unique value and must not contain NULL values. There can be only one PRIMARY KEY in a MySQL table. We can create a PRIMARY KEY column by defining a PRIMARY KEY constraint. For defining PRIMARY KEY constraint we must have to use PRIMARY KEY keyword while creating the table and it can be demonstrated in the following example −ExampleThe following query we have created a table named ‘student’ by defining the column ‘RollNo’ as PRIMARY KEY −mysql> Create Table Student(RollNo INT PRIMARY KEY, Name Varchar(20),   ... Read More

When we will add a UNIQUE constraint on the same column multiple times then MySQL will create the index on that column for a number of times we have added the UNIQUE constraint.ExampleSuppose we have the table ‘employee’ in which we have the UNIQUE constraint on ‘empid’ column. It can be checked form the following query −mysql> Describe employee; +------------+-------------+------+-----+---------+-------+ | Field      | Type        | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | empid      | int(11)     | YES  | UNI | NULL    |       | | ... Read More

Multicolumn UNIQUE indexes can also be removed in the same as we remove UNIQUE constraint from the table.ExampleIn this example, with the following query we have removed the multicolumn UNIQUE indexes on table ‘employee’ −mysql> DROP index id_fname_lname on employee; Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0The removal of UNIQUE indexes can be observed from the result sets of the following query −mysql> show index from employee; Empty set (0.00 sec) mysql> describe employee; +------------+-------------+------+-----+---------+-------+ | Field      | Type        | Null | Key | Default | Extra | ... Read More

For creating multicolumn UNIQUE indexes we need to specify an index name on more than one column. Following example will create a multicolumn index named ‘id_fname_lname’ on the columns ‘empid’, ’first_name’, ’last_name’ of ‘employee’ table −mysql> Create UNIQUE INDEX id_fname_lname on employee(empid, first_name, last_name); Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe employee; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | empid | int(11) | YES | MUL | NULL | | | first_name | varchar(20) | YES | | NULL | | | ... Read More

For dropping UNIQUE constraint from a MySQL table, first of all, we must have to check the name of the index created by the UNIQUE constraint on the table. As we know that SHOW INDEX statement is used for this purpose. The ‘key_name’ in the result set of SHOW INDEX statement contains the name of the index. Now either with the help of DROP INDEX statement or ALTER TABLE statement, we can drop the UNIQUE constraint. The syntax for both the statements is as follows −SyntaxDROP INDEX index_name ON table_name; OR ALTER TABLE table_name DROP INDEX index_name;ExampleSuppose we have the ... Read More

SHOW INDEX statement is used to check the indexes created by a UNIQUE constraint on a MySQL table.SyntaxSHOW INDEX from table_name;ExampleSuppose we have the table ‘empl’ which have a UNIQUE constraint on column ‘empno’.mysql> describe empl; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | empno  | int(11)     | YES  | UNI | NULL    |       | | F_name | varchar(20) | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+ 2 rows in set (0.23 sec)Now as we know that ... Read More

CREATE UNIQUE INDEX statement can also be used to apply the UNIQUE constraint to the field of an existing MySQL table. The syntax of it is as follows −CREATE UNIQUE INDEX index_name ON table_name(Column_name);ExampleSuppose we have the following table named ‘Test5’ and we want to add UNIQUE constraint to the column ‘ID’ then it can be done with the help of CREATE UNIQUE INDEX command as follows −mysql> DESCRIBE TEST5; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID    | int(11)     | YES  |   ... Read More

We can apply the UNIQUE constraint to a column of an existing MySQL table with the help of ALTER TABLE statement.SyntaxALTER TABLE table_name MODIFY colum_name datatype UNIQUE;                     OR ALTER TABLE table_name ADD UNIQUE (colum_name);ExampleSuppose we have the following table named ‘Test4’ and we want to add UNIQUE constraint to the column ‘Name’ then it can be done with the help of ALTER TABLE command as follows −mysql> DESCRIBE test4; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID   ... Read More