The following is a simple example that illustrates Foreign Key constraints:
Creating a table for manager:
CREATE TABLE manager (id integer primary key auto_increment, name text) ENGINE=InnoDB;
Creating a table for club and creating a foreign key constraint for manager_id with key 'manager_id_reference':
CREATE TABLE club (id integer primary key auto_increment, is_accessible char, manager_id integer NOT NULL) ENGINE=InnoDB;
ALTER TABLE club ADD CONSTRAINT manager_id_reference FOREIGN KEY(manager_id) REFERENCES manager (id);
Inserting some managers:
INSERT INTO manager(name) VALUES('Pritom');
INSERT INTO manager(name) VALUES('Kumar');
Inserting some clubs:
INSERT INTO club(is_accessible, manager_id) VALUES(true, 1);
INSERT INTO club(is_accessible, manager_id) VALUES(false, 2);
Let see manager table data (SELECT * FROM manager):
Let see club table data (SELECT * FROM club):
| ID |
IS_ACCESSIBLE |
MANAGER_ID |
| 1 |
1 |
1 |
| 2 |
0 |
2 |
Let see manager table structure (DESCRIBE manager):
| FIELD |
TYPE |
NULL |
KEY |
DEFAULT |
EXTRA |
| id |
int(11) |
NO |
PRI |
(null) |
auto_increment |
| name |
text |
YES |
|
(null) |
|
Let see club table structure (DESCRIBE club):
| FIELD |
TYPE |
NULL |
KEY |
DEFAULT |
EXTRA |
| id |
int(11) |
NO |
PRI |
(null) |
auto_increment |
| is_accessible |
char(1) |
YES |
|
(null) |
|
| manager_id |
int(11) |
NO |
MUL |
(null) |
|
Now try to delete manager:
DELETE FROM USER WHERE id = 1;
will error as:
Schema Creation Failed: Cannot delete or update a parent row: a foreign
key constraint fails (`db_2_8d4a1`.`club`, CONSTRAINT
`manager_id_reference` FOREIGN KEY (`manager_id`) REFERENCES `manager`
(`id`)):
This happens because the data in the manager table depends on the data in the club table.
Full
sqlFiddle
