Home Sql Mysql-alter Mysql Alter Foreign Key
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Mysql Alter Foreign Key

Advertisement
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

     

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)
Advertisement

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 16, 2009

Ask Questions?    Discuss: Mysql Alter Foreign Key  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Harish Gyanani
February 15, 2013
an error discussion

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`); ) above query did not work,if we remove single quotes from table name and column names then it works right query is- 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) );
swati
May 10, 2012
About Foreign key constraint

I have 2 tables,bus(bno int pk,capacity) & route(rno pk,source char(20)) having many to 1 relationship,but at the time table creation i forgot to add foreign key constraint so how to add fk constraint? please help me.
DMCA.com