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":
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)
|
Query to Alter table Book Add Foreign Key contraint:
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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
