We can add a FOREIGN KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement. Syntax ALTER TABLE table_name ADD FOREIGN KEY (colum_name) REFERENCES table having Primary Key(column_name); Example Suppose we want to add a FOREIGN KEY constraint on the table ‘Orders1’ referencing to the table ‘Customer’ which have column ‘Cust_Id’ as the Primary Key. It can be done with the help of the following query − mysql> Alter table orders1 add FOREIGN KEY(Cust_id) REFERENCES Customer(Cust_id); Query OK, 0 rows affected (0.21 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> ... Read More

We can remove composite PRIMARY KEY constraint from multiple columns of an existing table by using DROP keyword along with ALTER TABLE statement.ExampleSuppose we have a table ‘Room_allotment’ having a composite PRIMARY KEY constraint on columns ‘ID’ and ‘RoomNo’ as follows −mysql> describe room_allotment; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id     | int(11)     | NO   | PRI | 0       |       | | Name   | varchar(20) | NO   | PRI |         ... Read More

We can set PRIMARY KEY constraint on multiple columns of an existing table by using ADD keyword along with ALTER TABLE statement.ExampleSuppose we have a table ‘Room_allotment’ as follows −mysql> Create table Room_allotment(Id Int, Name Varchar(20), RoomNo Int); Query OK, 0 rows affected (0.20 sec) mysql> Describe Room_allotment; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | Id     | int(11)     | YES  |     | NULL    |       | | Name   | varchar(20) | YES  |     | NULL ... Read More

Actually, MySQL allows us to set PRIMARY KEY on multiple columns. The advantage of doing this is that we can work on multiple columns as a single entity. Example We have created the table allotment by defining composite PRIMARY KEY on multiple columns as follows − mysql> Create table allotment( RollNo Int, Name Varchar(20), RoomNo Int, PRIMARY KEY(RollNo, RoomNo)); Query OK, 0 rows affected (0.23 sec) mysql> Describe allotment; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | RollNo ... Read More

As we know the AUTO_INCREMENT column must have the PRIMARY KEY constraint on it also hence when we will try to drop PRIMARY KEY constraint from the AUTO_INCREMENT column the MySQL returns an error message regarding the incorrect table definition. The example below will demonstrate it −ExampleSuppose we have ‘Accounts’ table having the following description −mysql> Describe accounts; +--------+-------------+------+-----+---------+----------------+ | Field  | Type        | Null | Key | Default | Extra          | +--------+-------------+------+-----+---------+----------------+ | Sr     | int(10)     | NO   | PRI | NULL    | auto_increment | ... Read More

We can remove PRIMARY KEY constraint from a column of an existing table by using DROP keyword along with ALTER TABLE statement.ExampleSuppose we have a table ‘Player’ having a PRIMARY KEY constraint on column ‘ID’ as follows −mysql> DESCRIBE Player; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID    |  int(11)    | NO   | PRI | NULL    |       | | Name  | varchar(20) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+ 2 rows in ... Read More

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

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