1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
| --------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `ville`
--------------
--------------
create table `ville`
( `ville_id` integer unsigned not null auto_increment primary key,
`ville_nom` varchar(255) not null
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `ville` (`ville_nom`) values
('Abidjan'),('Bouaké'),('Yamoussoukro')
--------------
--------------
select * from `ville`
--------------
+----------+--------------+
| ville_id | ville_nom |
+----------+--------------+
| 1 | Abidjan |
| 2 | Bouaké |
| 3 | Yamoussoukro |
+----------+--------------+
--------------
DROP TABLE IF EXISTS `quartier`
--------------
--------------
create table `quartier`
( `quartier_id` integer unsigned not null auto_increment primary key,
`quartier_nom` varchar(255) not null,
`ville_id` integer unsigned not null ,
constraint `quartier_ville` foreign key (`ville_id`) references `ville` (`ville_id`) on delete cascade on update cascade
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `quartier` (`quartier_nom`,`ville_id`) values
('Abobo', 1),('Yopougon', 1),
('Dougouba', 2), ('Nimbo', 2),
('Assabou', 3),('Kokrenou', 3)
--------------
--------------
select * from `quartier`
--------------
+-------------+--------------+----------+
| quartier_id | quartier_nom | ville_id |
+-------------+--------------+----------+
| 1 | Abobo | 1 |
| 2 | Yopougon | 1 |
| 3 | Dougouba | 2 |
| 4 | Nimbo | 2 |
| 5 | Assabou | 3 |
| 6 | Kokrenou | 3 |
+-------------+--------------+----------+
--------------
insert into `quartier` (`quartier_nom`,`ville_id`) values
('bla bla bla', 4)
--------------
ERROR 1452 (23000) at line 75: Cannot add or update a child row: a foreign key constraint fails (`base`.`quartier`, CONSTRAINT `quartier_ville` FOREIGN KEY (`ville_id`) REFERENCES `ville` (`ville_id`) ON DELETE CASCADE ON UPDATE CASCADE)
--------------
select * from `quartier`
--------------
+-------------+--------------+----------+
| quartier_id | quartier_nom | ville_id |
+-------------+--------------+----------+
| 1 | Abobo | 1 |
| 2 | Yopougon | 1 |
| 3 | Dougouba | 2 |
| 4 | Nimbo | 2 |
| 5 | Assabou | 3 |
| 6 | Kokrenou | 3 |
+-------------+--------------+----------+
--------------
select t2.ville_id,
t2.ville_nom,
t1.quartier_id,
t1.quartier_nom
from `quartier` as t1
inner join `ville` as t2
on t2.ville_id = t1.ville_id
order by t2.ville_id, t1.quartier_id
--------------
+----------+--------------+-------------+--------------+
| ville_id | ville_nom | quartier_id | quartier_nom |
+----------+--------------+-------------+--------------+
| 1 | Abidjan | 1 | Abobo |
| 1 | Abidjan | 2 | Yopougon |
| 2 | Bouaké | 3 | Dougouba |
| 2 | Bouaké | 4 | Nimbo |
| 3 | Yamoussoukro | 5 | Assabou |
| 3 | Yamoussoukro | 6 | Kokrenou |
+----------+--------------+-------------+--------------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager