Mysql Alter Foreign Key

A Foreign Key is a field that marks to the primary key of another table. The use of the foreign key is used to ensure referential integrity of the data.

Mysql Alter Foreign Key

Mysql Alter Foreign Key

     

A Foreign Key is a field that marks to the primary key of another  table. The use of the foreign key is used to ensure referential integrity of the data.

Understand with Example

The Tutorial illustrate an example from 'Mysql Alter Foreign Key'. To understand with example we create a table 'publisher'. The Table 'publisher' has primary field name 'publisher_id'.  

Query to create table "publisher" :

publisher CREATE TABLE `publisher` ( 
`publisher_id` decimal(10,0) NOT NULL, 
`publisher_name` varchar(50) NOT NULL, 
`contact_name` varchar(50) default NULL, 
PRIMARY KEY (`publisher_id`); 
)

Describe the table "publisher" :

The Describe show you the Fieldname, Data Type, Null etc of table publisher.

    mysql> describe publisher;
      +----------------+---------------+------+-----+---------+-------+
      | Field          | Type          | Null | Key | Default | Extra |
      +----------------+---------------+------+-----+---------+-------+
      | publisher_id   | decimal(10,0) | NO   | PRI |         |       |
      | publisher_name | varchar(50)   | NO   |  |         |       |
      | contact_name   | varchar(50)   | YES  |     |         |       |
      +----------------+---------------+------+-----+---------+-------+
      3 rows in set (0.02 sec)

Query to create table "book":

The Query create table is used to create a table book with required fieldname and data type respectively.

CREATE TABLE `book` ( 
`book_id` decimal(10,0) NOT NULL, 
`publisher_id` decimal(10,0) NOT NULL, 
`publisher_name` varchar(50) NOT NULL 
)

Describe table "book":

Query to Alter table Book Add Foreign Key contraint:

mysql> describe book;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| book_id | decimal(10,0) | NO | | | |
| publisher_id | decimal(10,0) | NO | | | |
| publisher_name | varchar(50) | NO | | | |
+----------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


The Query Alter is used to change the structure of existing table 'books' and add constraint foreign key 'publisher_id and publisher_name' that add the referential integrity of the data from parent table to child table.

mysql> ALTER TABLE book add CONSTRAINT fk_publisher FOREIGN KEY (publisher_id, publisher_name) REFERENCES publisher(publisher_id, publisher_name);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

Describe table "book"

mysql> describe book;
      +----------------+---------------+------+-----+---------+-------+
      | Field          | Type          | Null | Key | Default | Extra |
      +----------------+---------------+------+-----+---------+-------+
      | book_id        | decimal(10,0) | NO   |     |         |       |
      | publisher_id   | decimal(10,0) | NO   | MUL |         |       |
      | publisher_name | varchar(50)   | NO   |     |         |       |
      +----------------+---------------+------+-----+---------+-------+
      3 rows in set (0.02 sec)