2
: . . | : 3- -1644 . . |
2016
2 (2 .).
: ᒺ. .
: sql. HeidiSQL. ᒺ . . . .
:
1. ( ), .
2. 10-20 ( , ).
3. .
4. , , .
7: , , .
:
. 1 ( )
:
1). CREATE TABLE `groups` (`id_group` INT(10) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(9), PRIMARY KEY (`id_group`)) COLLATE='utf8_general_ci';
2). CREATE TABLE `laboratories` (`number_room` INT(3) NOT NULL, `title` VARCHAR(100), `responsible` VARCHAR(25), `provision` INT(3), PRIMARY KEY (`number_room`)) COLLATE='utf8_general_ci';
3). CREATE TABLE `predmets` (`id_predmet` INT(10) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100), `count_hour` INT(3), `start_date` DATE, `stop_date` DATE, PRIMARY KEY (`id_predmet`)) COLLATE='utf8_general_ci';
4). create table student(id_student int(10) not null auto_increment primary key, name varchar(15), surname varchar(20), sex varchar(1), group_id int(10)) COLLATE='utf8_general_ci';
5). create table rozklad(group_id int(10), id_predmet int(10), room_number int(3)) COLLATE='utf8_general_ci';
:
1). Alter table student add foreign key (group_id) references groups(id_groups) on update cascade;
1). Alter table rozklad add foreign key (group_id) references groups(id_groups) on update cascade;
1). Alter table rozklad add foreign key (id_predmet) references predmets(id_predmet) on update cascade;
1). Alter table rozklad add foreign key (room_number) references laboratories(number_room) on update cascade;
:
1). Insert into groups values(null, -1644);
2). Insert into student values(null, , , , 1);
3). Insert into laboratories values(605, , . ., 100);
4). Insert into predmets values(null, , 40, cast('2016.10.20' as datetime), cast('2016.12.01' as datetime));
5). Insert into rozklad values(1, 1, 605);
. 2 groups
|
|
. 3 student
. 4 Laboratories
. 5 rozklad
. 6 predmets
rozklad , :
insert into rozklad values(22, 32, 631);
:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`lab_db_1`.`rozklad`, CONSTRAINT `rozklad_ibfk_1` FOREIGN KEY (`
group_id`) REFERENCES `groups` (`id_group`) ON UPDATE CASCADE)
laboratories 605 636 , rozklad:
update laboratories set number_room=636 where number_room=605;
. 7 rozkld room_number
HeidiSQL, insert into, , , .