Re: selecting foreign key into a column
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.