MySQL Forums
Forum List  »  Quality Assurance

Re: selecting foreign key into a column
Posted by: Harshada Thakur
Date: May 22, 2013 12:06AM

When I talk about SQL databases, I always mean a set of tables with some relations between them. Relations in the databases are possible due to foreign keys. In the post I’m going to make a quick overview of the set up of foreign keys in MySQL using Toad MySQL. MySQL has strict standards, so Toad MySQL shouldn’t create many restrictions for porting the steps to any other MySQL development tool.

Theory

Creation of foreign key implies existing of at least two tables (InnoDB) in the database. One of the tables should be a parent table and the other a child. The child table must have a field corresponding to the parent’s field and have the same data type and length as parent’s one has.



view plaincopy to clipboardprint?
CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Foreign key always leads from a child table to a parent table. That’s why both tables have fields with similar properties.
After the short theoretical introduction I’m going to proceed to practice.

Creation of foreign key in Toad MySQL

In the previous paragraph I have shown how to create two tables which we will use later on. Firstly, I’m going to create a new index in the child table for the parent_id column. To do this I need to click on the appropriate button in Toad MySQL, and select the column for the new indexing:



And after that, script for the table will be changed to:

view plaincopy to clipboardprint?
CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id_index` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The second step is to declare the new constraint for the child table:



The constraint contains two main components:

The representation of the parent’s column for the foreign key (parent_id) in the child table;
The destination column in the parent table (id).
And after that, script for the table will changed to:

view plaincopy to clipboardprint?
CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id_index` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Also you can specify behavior of the foreign key on delete and on update actions in the constraint dialog window.
In the end we get two tables coupled with each other by foreign key.

Hopefully this data will help you out, to sort out your problem or else you can use this data as an additional information.

might be there other ways also available to sort out your problem, if i find something easyway to sort it out i will definitely help you with easiest way.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: selecting foreign key into a column
2781
May 22, 2013 12:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.