Referencing two tables in MYSQL
·
It is a
database concept for building and maintaining logical relationships between tables.
·
It is a very
useful and important part of RDBMS.
·
For the
referencing of two tables use the Referential integrity concept.
·
Referential
integrity is the combination of a primary key and a foreign
key.
·
The main
concept is that it does not allow the addition of any record in a table that contains
the foreign key unless the
reference table contains a corresponding primary key.
·
If any record
in the referenced table (i.e. the table that contains the primary key) is deleted.
·
The foreign key is used to link one or more
than one table together. It is also known as the referencing key.
·
A foreign key matches the primary key field
of another table. It means a foreign key field in one table refers to the
primary key field of the other table.
·
It identifies each row of another table
uniquely that maintains the referential
integrity in MySQL.
·
it creates a parent-child relationship with
the tables.
·
In this relationship, the parent table holds
the initial column values, and the column values of the child table reference the
parent column values.
·
MySQL allows us to define a foreign key
constraint on the child table.
Example: table:
agents
|
| AGENT_CODE | AGENT_NAME WORKING_AREA | COMMISSION PHONE_NO
COUNTRY | |
|
| A007 |
Ramasundar | Bangalore |
0.15 | 077-25814763 |
| |
|
| A003 |
Alex | London | 0.13 |
075-12458969 | | |
|
| A008 |
Alford | New York |
0.12 | 044-25874365 |
| |
|
| A011 |
Ravi Kumar | Bangalore |
0.15 | 077-45625874 |
| |
|
| A010 |
Santakumar | Chennai |
0.14 | 007-22388644 |
| |
|
| A012 |
Lucida | San Jose |
0.12 | 044-52981425 |
| |
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE |
|
200114 3500 2000 15-AUG-08 C00002 A008 |
|
200122 2500 400 16-SEP-08 C00003 A004 |
|
200118 500
100 20-JUL-08 C00023 A006 |
|
200119 4000 700 16-SEP-08 C00007 A010 |
|
200121 1500 600 23-SEP-08 C00008 A004 |
The following
conditions are -
1. 'agent_code' is primary key in 'agents'
table.
2. 'agent_code' is a foreign key in orders
table which is referring to the primary key of 'agents' table.
3. 'agent_code' of 'agents' and 'orders'
must be same.
SQL statement :
SELECT agents.agent_code,agents.agent_name, orders.ord_num,orders.advance_amount
FROM agents,orders WHERE agents.agent_code=orders.agent_code;
Output:
AGENT_CODE AGENT_NAME ORD_NUM ADVANCE_AMOUNT
---------- ---------------------------------------- ---------- ----------------------------------------------------------
A008 Alford 200114 2000
A004 Ivan 200122 400
A006 McDen 200118 100
A010 Santakumar 200119 700
A004 Ivan 200121 600
A011 Ravi Kumar 200130 400
MySQL defines the foreign key in two
ways:
- Using
CREATE TABLE Statement
- Using
ALTER TABLE Statement
1.
Using CREATE TABLE
Statement
Syntax
Following are the basic syntax used for
defining a foreign key using CREATE TABLE OR ALTER TABLE statement in the
MySQL:
1. [CONSTRAINT constraint_name]
2.
FOREIGN KEY [foreign_key_name] (col_name, ...)
3. REFERENCES parent_tbl_name (col_name,...)
4.
ON DELETE referenceOption
5. ON UPDATE referenceOption
the following parameters are:
constraint_name: It
specifies the name of the foreign key constraint. If we have not provided the
constraint name, MySQL generates its name automatically.
col_name: It
is the name of the column that we are going to make a foreign key.
parent_tbl_name: It
specifies the name of a parent table followed by column names that reference
the foreign key columns.
Refrence_option: It
is used to ensure how the foreign key maintains referential integrity using ON
DELETE and ON UPDATE clauses between the parent and child table.
Foreign Key
Example
create a database named "mysqltestdb"
with the command below:
1. mysql> CREATE DATABASE mysqltestdb;
2.
mysql> use mysqltestdb;
create two tables named "customer"
and "contact":
Table: customer
CREATE TABLE customer ( ID INT NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL, City varchar(50) NOT NULL, PRIMARY KEY (ID) );
Table: contact
CREATE TABLE contact ( ID INT, Customer_Id INT, Customer_Info varchar(50)
NOT NULL,
Type varchar(50) NOT NULL, INDEX par_ind (Customer_Id),
CONSTRAINT fk_customer FOREIGN KEY (Customer_Id) REFERENCES customer(ID)
ON DELETE CASCADE ON UPDATE CASCADE);
Insert Data to
the Table
INSERT INTO customer(Name, City) VALUES ('Joseph', 'California'), ('Mary', 'NewYork'), ('John', 'Alaska');
INSERT INTO contact (Customer_Id, Customer_Info, Type) VALUES
(1, 'Joseph@javatpoint.com', 'email'), (1, '121-121-121', 'work' ), (1, '123-123-123', 'home'),
(2, 'Mary@javatpoint.com', 'email'), (2, 'Mary@javatpoint.com', 'email'),
(2, '212-212-212', 'work'), (3, 'John@javatpoint.com', 'email'), (3, '313-313-313', 'home');
to delete the referential data that removes records
from both tables. We have defined the foreign key in the contact table as:
FOREIGN KEY (Customer_Id) REFERENCES customer(ID) ON DELETE CASCADE
ON UPDATE CASCADE.
It means if we delete any customer record
from the customer table, then the related records in the contact table should
also be deleted. And the ON UPDATE CASCADE will updates automatically on the
parent table to referenced fields in the child table(Here, it is Customer_Id).
Deletes
a record from the table whose name is JOHN.
mysql> DELETE FROM customer WHERE Name='John';
the fields with the name JOHN will be removed
entirely from both tables.
ON UPDATE CASCADE.
update the Customer_Id of Mary in
the contact table:
mysql> UPDATE customer SET id=3 WHERE Name='Mary';
1.
Define Foreign Key Using ALTER TABLE
Statement
to add a foreign key to the column of an
existing table; then, this statement is used to add the foreign key for that
column.
Syntax
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (column_name, ...) REFERENCES table_name (column_name,...)
ON DELETE referenceOption ON UPDATE referenceOption
When we add a foreign key using the ALTER
TABLE statement, it is recommended to first create an index on
the column(s), which is referenced by the foreign key.
Example
Table: Person
CREATE TABLE Person ( ID INT NOT NULL AUTO_INCREMENT,
Name varchar(50) NOT NULL, City varchar(50) NOT NULL, PRIMARY KEY (ID) );
Table: Contact
CREATE TABLE Contact ( ID INT, Person_Id INT, Info varchar(50) NOT NULL,
Type varchar(50) NOT NULL );
to add a foreign key to an existing table, use
the ALTER TABLE statement as below:
ALTER TABLE Contact ADD INDEX par_ind ( Person_Id );
ALTER TABLE Contact ADD CONSTRAINT fk_person FOREIGN KEY ( Person_Id )
REFERENCES Person ( ID ) ON DELETE CASCADE ON UPDATE RESTRICT;
0 Comments