Warm tip: This article is reproduced from serverfault.com, please click
create-table foreign-keys mysql sql sql-insert

Why do I keep on getting errors like Error Code 1146 in MySQL?

发布于 2020-12-01 17:15:27

I keep on getting 1146 Error Code: 1146. Table 'world.itemsintransactions' doesn't exist, and before I kept on getting many other diffferent error codes that I eventually fix only to move onto another error code. Please bear in mind this is my first MySQL assignment, never used MySQL before this assignment, so apologies if I have missed out on something very obvious!

Here is my code...

Create DATABASE CS_Store;
USE CS_Store;

CREATE TABLE customers (
    birth_day date,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    c_id VARCHAR(20),
    CONSTRAINT PK_Customers PRIMARY KEY (c_id));
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1993-07-11','Victor','Davis',1);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('2001-03-28','Katarina','Williams',2);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1965-12-11','David','Jones',3);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1980-10-10','Evelyn','Lee',4);


CREATE TABLE employees (
    birth_day date,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    e_id int,
    CONSTRAINT PK_Employees PRIMARY KEY (e_id)
);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1983-09-02','David','Smith',1);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1990-07-23','Olivia','Brown',2);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1973-05-11','David','Johnson',3);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1999-11-21','Mia','Taylor',4);

CREATE TABLE transactions (
    e_id int,
    c_id int,
    date date,
    t_id int,
    CONSTRAINT PK_transactions PRIMARY KEY (t_id),
    FOREIGN KEY (e_id) REFERENCES employees(e_id),
    FOREIGN KEY (c_id) REFERENCES customers(c_id)
);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,1,'2020-8-11',1);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (3,1,'2020-8-15',2);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,4,'2020-9-01',3);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (2,2,'2020-9-07',4);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (4,3,'2020-9-07',5);


CREATE TABLE items (
    amount int,
    price_for_each int,
    name VARCHAR(20),
    CONSTRAINT PK_items PRIMARY KEY (name)
);
INSERT INTO items (price_for_each, amount, name) VALUES (110,22,'2lof milk');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'6 cans of lemonade');
INSERT INTO items (price_for_each, amount, name) VALUES (150,20,'Pack of butter');
INSERT INTO items (price_for_each, amount, name) VALUES (450,13,'Roast chicken');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'Pack of rice');
INSERT INTO items (price_for_each, amount, name) VALUES (20,50,'Banana');
INSERT INTO items (price_for_each, amount, name) VALUES (200,30,'3kg sugar');
INSERT INTO items (price_for_each, amount, name) VALUES (150,15,'Toast bread');
INSERT INTO items (price_for_each, amount, name) VALUES (150,18,'Earl Grey tea');

CREATE TABLE promotions (
    discount int,
    p_id int,
    CONSTRAINT PK_promotions PRIMARY KEY (p_id)
);
INSERT INTO promotions (discount, p_id) VALUES (99,1);
INSERT INTO promotions (discount, p_id) VALUES (200,2);
INSERT INTO promotions (discount, p_id) VALUES (150,3);
INSERT INTO promotions (discount, p_id) VALUES (150,4);

CREATE TABLE itemsinpromotions (
    name VARCHAR(20),
    p_id int,
    amount int,
    FOREIGN KEY (name) REFERENCES items(name),
    FOREIGN KEY (p_id) REFERENCES promotions(p_id)
);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('6 cans of lemonade',1,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Roast chicken',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Toast bread',3,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('2lof milk',4,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Banana',4,3);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('3kg sugar',4,2);

CREATE TABLE itemsintransactions (
    name VARCHAR(20),
    t_id int,
    amount int,
    FOREIGN KEY (name) REFERENCES items(name),
    FOREIGN KEY (t_id) REFERENCES transactions(t_id)
);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES('6 cans of lemonade',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',1,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',1,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',2,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',3,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',3,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',4,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',4,20);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',4,8);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Earl Grey tea',5,10);



CREATE VIEW DavidSoldTo AS 
    SELECT birth_day, first_name, last_name
    FROM customers
    WHERE EXISTS (SELECT 1 FROM Transactions WHERE e_id=1)
    ORDER BY birth_day
;

CREATE VIEW PeopleInShop AS 
    SELECT DISTINCT birth_day, first_name, last_name
    FROM Employees
    UNION
    SELECT DISTINCT birth_day, first_name, last_name
    FROM Customers
    WHERE EXISTS (SELECT 1 FROM Transactions WHERE date=2020-9-07)
    ORDER BY birth_day
;


CREATE VIEW ItemsLeft AS 
    SELECT amount AS amount_left, name
    FROM Items
    WHERE amount_left NOT IN (SELECT COUNT(t_id) FROM Transactions)
    GROUP BY name
;
    
    
    -- Not yet completed
CREATE VIEW PromotionItemsSatisfiedByTransactions AS
    SELECT name, p_id, COUNT(t_id) AS number_of_times
    FROM Promotions NATURAL JOIN Transactions
    SELECT number_of_times 
    WHERE number_of_times >= 1;c_id
Questioner
user14743675
Viewed
0
分享
GMB 2020-12-02 01:40:25

Apart from the error related to the schema name, that was explained by @ysth, there is another issue with your code. Consider these table declarations:

create table customers (
    birth_day date,
    first_name varchar(20),
    last_name varchar(20),
    c_id varchar(20),
    constraint pk_customers primary key (c_id)
);

create table transactions (
    e_id int,
    c_id int,
    date date,
    t_id int,
    constraint pk_transactions primary key (t_id),
    foreign key (e_id) references employees(e_id),
    foreign key (c_id) references customers(c_id)
);

The problem is that customers(c_id) is of datatype varchar(20), while referencing column transactions(c_id) is int. Because of that, the foreign key constraint creation fails, as does the creation of table transactions. The missing table propagates error through out the rest of the script.

Fix one of the datatype so they are identical. I would expect something called ..._id to be int. Then, the whole script should just work.

Demo on DB Fiddle

热门帖子

1
推荐一些好玩的/大众的手游
2
求指教后端项目迁移方案
3
迷你洗衣机是不是都是智商税？
4
求助一个排查了半年没解决的 MySQL order by 子句导致索引失效的问题， 500 多万条记录的小表要查快两分钟
5
个人开发了一款 WordPress 主题： iPao，集成了 AI 总结功能
6
偶然发现奇游加速器会在系统里植入根证书
7
国内有蒲公英替代品推荐吗？
8
语音助手这个东西真的会监听谈话并且上传，从而泄漏隐私吗？
9
出一些有意思的域名-明盘
10
jetbrains 全家桶升级 2024 后，在滚动代码时候感觉有点掉帧

热门github

1
Implementation of paper - YOLOv9: Learning What You Want to Learn Using Programmable Gradient Information
2
A Windows and Office activator using HWID / Ohook / KMS38 / Online KMS activation methods, with a focus on open-source code and fewer antivirus detections.
3
Get up and running with Llama 2, Mistral, Gemma, and other large language models.
4
该项目可以让你通过订阅的方式使用Cloudflare WARP+，自动获取流量。This project enables you to use Cloudflare WARP+ through subscription, automatically acquiring traffic.
5
Multi functional app to find duplicates, empty folders, similar images etc.
6
Xray panel supporting multi-protocol multi-user expire day & traffic & ip limit (Vmess & Vless & Trojan & ShadowSocks & Wireguard)
7
The Free Software Media System
8
lightweight, standalone C++ inference engine for Google's Gemma models.
9
📚 Freely available programming books
10
A collective list of free APIs
11
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
12
🎓 Path to a free self-taught education in Computer Science!
13
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
14
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
15
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.