We have two tables company and location. First table manages company details and second table stores location detail of company. one company can span in multiple location.
Initially we have created table for company and location as below.
1 2 3 4 | create table company(id int(11) NOT NULL AUTO_INCREMENT, name varchar(50), description varchar(200), PRIMARY KEY(id)); |
1 2 3 4 | create table location(id int(11) NOT NULL AUTO_INCREMENT, name varchar(50), address varchar(200), PRIMARY KEY(id)); |
checking the description of both table
1 2 3 4 5 6 7 8 9 | mysql> desc company; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | description | varchar(200) | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.09 sec) |
1 2 3 4 5 6 7 8 9 | mysql> desc location; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | address | varchar(200) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ 3 rows in set (0.06 sec) |
Now We find that one company can exists in multiple location so there is one to many relationship between company to location and to access location detail of company we have to create foreign key reference of company to location table.
We have already create location table so without deleted it again we will alter table to add foreign key refrence of company.
For adding new foreign key column we have to first add a column to location table then we will make it foreign key.
Before proceeding lets see the syntax of alter table command
Syntax
1 2 3 4 5 6 | ALTER TABLEL table2 add CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES table1(index_column) ON UPDATE reference_option ON DELETE reference_option |
1 2 3 4 5 6 7 8 9 10 11 | //adding new column // ALTER TABLE location add company_id INT(11) NOT NULL; //making company_id column as foreign key ALTER TABLE location add CONSTRAINT company_fk_id FOREIGN KEY (company_id) REFERENCES company(id) ON UPDATE CASCADE ON DELETE CASCADE; |
Checking location description you will find that a company_id field is added to location table and it is reffeering to company tables
1 2 3 4 5 6 7 8 9 10 | mysql> desc location; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | address | varchar(200) | YES | | NULL | | | company_id | int(11) | NO | MUL | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) |
To show detailed description you can use
1 | SHOW CREATE TABLE location |