Actually, foreign keys enforce referential integrity that helps us to maintain the consistency and integrity of the data automatically. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. We cannot create an order for a non-existent customer. It can be demonstrated by inserting the values in both the tables as follows −mysql> Select * from Customer; +----+--------+ | id | name   | +----+--------+ | 1  | Gaurav | | 2  | Raman  | | 3  | Harshit| | 4  | Aarav  | ... Read More

The relationship between parent and child table is One-to-Many relationship. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. The relationship is one-to—many because a customer can have more than one order. It can be demonstrated by inserting the values in both the tables as follows −mysql> Select * from Customer; +----+---------+ | id | name    | +----+---------+ | 1  | Gaurav  | | 2  | Raman   | | 3  | Harshit | | 4  | Aarav   | +----+---------+ ... Read More

MySQL allows us to add a FOREIGN KEY constraint on more than one field in a table. The condition is that each Foreign Key in the child table must refer to the different parent table.ExampleSuppose we have a table ‘customer2’ which have a Primary Key constraint on the field ‘cust_unq_id’ as follows −mysql> describe customer2; +-------------+-------------+------+-----+---------+-------+ | Field       | Type        | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | cust_id     | int(11)     | YES  |     | NULL    |       | | First_name  | ... Read More

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