Objective and Introduction
Objective: This segment introduces how to link two tables using FOREIGN KEY.
Introduction: In relational databases, a foreign key is a column (field) or group of columns. A parent table stores the primary key, and the child table references the parent with a foreign key. Refer to the following syntax for creating such references, or to link the tables using a foreign key.
Selecting all the records from a parent table
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | DB | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> USE DB; Database changed mysql> SHOW TABLES; +--------------+ | Tables_in_DB | +--------------+ | holders | +--------------+ 1 row in set (0.01 sec) mysql> SELECT * FROM holders; +-------------+---------------+---------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+---------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348990 | Donald Taylor | Irvine | 1990-08-20 | Citi | 7000 | | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348992 | Ryan Bakshi | Mumbai | 1982-01-14 | Citi | 50000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | | 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 | | 25622348995 | James Moore | NULL | 1978-06-26 | Citi | 97800 | +-------------+---------------+---------+------------+----------+--------+ 7 rows in set (0.00 sec) mysql> DESC holders; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | account_no | bigint | NO | PRI | NULL | | | name | varchar(30) | NO | | NULL | | | city | varchar(20) | YES | | NULL | | | dob | date | YES | | NULL | | | bank | varchar(10) | YES | | NULL | | | amount | bigint | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Creating a table with foreign key
mysql> CREATE TABLE loans(
-> loan_id INT AUTO_INCREMENT PRIMARY KEY,
-> loan_type VARCHAR(20) NOT NULL,
-> loan_amount BIGINT,
-> account_no BIGINT,
-> FOREIGN KEY(account_no) REFERENCES holders(account_no));
Query OK, 0 rows affected (1.79 sec)
mysql> DESC loans;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| loan_id | int | NO | PRI | NULL | auto_increment |
| loan_type | varchar(20) | NO | | NULL | |
| loan_amount | bigint | YES | | NULL | |
| account_no | bigint | YES | MUL | NULL | |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> SHOW TABLES;
+--------------+
| Tables_in_DB |
+--------------+
| holders |
| loans |
+--------------+
2 rows in set (0.00 sec)
Comments and Reactions