Cannot add foreign key constraint - MySQL
Формулировка задачи:
CREATE TABLE salespeople (sid integer not null auto_increment, sname text not null, comm decimal(5,5), primary key (sid)) engine = InnoDB Default charset = utf8;
Решение задачи: «Cannot add foreign key constraint»
textual
Листинг программы
CREATE TABLE salespeople (sid int not null auto_increment, sname text not null, comm decimal(5,5), primary key (sid)) engine = InnoDB Default charset = utf8; INSERT INTO salespeople (sid, sname, comm) VALUES (1, "Ivan Jadov", "0.12"), (2, "Fedor Zahapov", "0.15"), (3, "Maxim Petrov", "0.14"), (4, "Alisa Tir", "0.12"); CREATE TABLE categories (pcategory_id int not null auto_increment, pcname text not null, primary key (pcategory_id)) engine = InnoDB Default charset = utf8; INSERT INTO categories (pcategory_id, pcname) VALUES (1, "Kettles"), (2, "Microwaves"), (3, "Televisions"); CREATE TABLE products (pid int not null auto_increment, pbrand text, pcategory_id int, pname text, pquantity int, pcost decimal (15,2), primary key (pid), foreign key(pcategory_id) references categories(pcategory_id) ON UPDATE CASCADE) engine = InnoDB Default charset = utf8; INSERT INTO products (pid, pbrand, pcategory_id, pname, pquantity, pcost) VALUES (1, "Samsung", 1, "Exhaust", 3, "650.5"), (default, "Samsung", 1, "Fountain", 5, "790.0"), (default, "Black mesa", 2, "Black mesa-1", 7, "2200.0"), (default, "Black mesa", 3, "Emitter-3", 9, "9000.0"); CREATE TABLE orders (oid int not null auto_increment, odate DATE, amt DECIMAL(15,2), pid int, sid int, primary key(oid), foreign key(pid) references products(pid) ON DELETE CASCADE ON UPDATE CASCADE, foreign key(sid) references salespeople(sid) ON DELETE CASCADE ON UPDATE CASCADE) engine = InnoDB Default charset = utf8; INSERT INTO orders (oid, odate, amt, pid, sid) VALUES (default, "2009-09-1", "650.5", 1, 1), (default, "2009-09-10", "790.0", 2, 2), (default, "2009-10-1", "2200.0", 3, 3), (default, "2009-11-15", "9000.0", 4, 1), (default, "2009-12-4", "650.5", 1, 2);
Объяснение кода листинга программы
- Создание таблицы
salespeopleс полямиsid,sname,commи настройками для ИнниДБ. - Вставка данных в таблицу
salespeople. - Создание таблицы
categoriesс полямиpcategory_id,pcnameи настройками для ИнниДБ. - Вставка данных в таблицу
categories. - Создание таблицы
productsс полямиpid,pbrand,pcategory_id,pname,pquantity,pcost,primary key (pid)и настройками для ИнниДБ. - Вставка данных в таблицу
products. - Создание таблицы
ordersс полямиoid,odate,amt,pid,sid,primary key(oid),foreign key(pid) references products(pid) ON DELETE CASCADE ON UPDATE CASCADE,foreign key(sid) references salespeople(sid) ON DELETE CASCADE ON UPDATE CASCADEи настройками для ИнниДБ. - Вставка данных в таблицу
orders.