MySQL Tutorial
A foreign key is a field or a column that is used to establish a link between two tables.
A foreign key in one table used to point primary key in another table.
CREATE TABLE table_name (
column1 data_type(size)
column2 data_type(size)
PRIMARY KEY (columns),
FOREIGN KEY (column) REFERENCES Persons(column)
);
The following MySQL creates a FOREIGN KEY on the "ROLL_NO" column when the "Teachers" table is created:
CREATE TABLE Teachers
(
TECH_NO int(3) NOT NULL ,
NAME varchar(20) NOT NULL,
SUBJECT varchar(20) ,
PRIMARY KEY (TECH_NO),
FOREIGN KEY (ROLL_NO) REFERENCES Students(ROLL_NO)
); This would produce the following result.
The "ROLL_NO" column in the "Teachers" table points to the "ROLL_NO" column in the "Students" table.
The "ROLL_NO" column in the "Students" table is the PRIMARY KEY in the "Students" table.
The "ROLL_NO" column in the "Teachers" table is a FOREIGN KEY in the "Teachers" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
To create a FOREIGN KEY constraint on the "ROLL_NO" column when the "Teachers" table is already created, use the following MySQL:
ALTER TABLE Teachers
ADD FOREIGN KEY (ROLL_NO) REFERENCES Students(ROLL_NO); To drop a FOREIGN KEY constraint, use the following MySQL:
ALTER TABLE Teachers
DROP FOREIGN KEY ROLL_NO;
| ROLL_NO | NAME | SUBJECT |
|---|---|---|
| 1 | Will | C++ |
| 2 | SAM | Python |
| 3 | Rani | Node.js | 4 | Rim | Java |
| 5 | Micheal | JAVA |
| 6 | Lara | PHP |
| TECH_NO | NAME | SUBJECT | ROLL_NO |
|---|---|---|---|
| 20 | Kayla | PHP | 6 |
| 21 | Albert | Java | 2 |
| 21 | Albert | Java | 5 |
| 22 | Helen | Node.js | 3 | 23 | Anne | Python | 2 |
| 24 | Jaime | C++ | 1 |
Sample Data Meaning