SQL - FOREIGN KEY
Foreign Keys and referential constraints allow to set relationships between tables and modify some of the database engine’s actions. A foreign key is a column that uniquely references a record in another table.
For Example:
Create a table named 'EMPLOYEE' and set relationship with the table 'DEPARTMENT'.
mysql> create table employee (emp_id int(3) primary key,
-> emp_name varchar(30) not null,
-> desig varchar(20),
-> dept_code varchar(3),
-> date_join date,
-> basic_pay decimal(10,2),
-> da decimal(10,2),
-> gross_pay decimal(10,2),
-> foreign key(dept_code)
-> references department(dept_id));
Query OK, 0 rows affected (0.71 sec)
mysql>
To see the structure:
mysql> desc employee; +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | emp_id | int(3) | NO | PRI | NULL | | | emp_name | varchar(30) | NO | | NULL | | | desig | varchar(20) | YES | | NULL | | | dept_code | varchar(3) | YES | MUL | NULL | | | date_join | date | YES | | NULL | | | basic_pay | decimal(10,2) | YES | | NULL | | | da | decimal(10,2) | YES | | NULL | | | gross_pay | decimal(10,2) | YES | | NULL | | +-----------+---------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql>