php - 关于外键的 SQL 语法错误

Question

请有人帮我弄清楚为什么我在尝试运行 SQL 文件时可能会收到以下语法错误。这与我在表 cyclist 中创建的 foreign key t_id 有关。然而，一切都被引用等并且运行良好。直到我尝试用它来输入数据......一定有什么东西是我遗漏的

第 15 行的错误 1054 (42S22):“字段列表”中的未知列“t_id”

下面是给我错误(输入数据)的 SQL，在下面我复制了我创建的表列表。谢谢。

INSERT INTO bike (id, bike_brand, cost, colour) 
VALUES
(NULL, 'Canyon Aeroad', '£3500', 'White'),
(NULL, 'Scott Addict', '£4000', 'Black'),
(NULL, 'BMC Timemachine', '£2800', 'Matt Black'),
(NULL, 'Pinarello Dogma F8', '£5800', 'Blue');

INSERT INTO team (id, team_name, main_colour, b_id, year_established, sponsor, budget)
VALUES
(NULL, 'Movistar', 'Purple', NULL, '2003', 'MOVISTAR', '£20,433,043'),
(NULL, 'Orica Greenedge', 'Green', Null, '2005', 'Greenedge', '£30,123,448'),
(NULL, 'BMC', 'Red', NULL, '2008', 'BMC Switzerland', '£49,432,123'),
(NULL, 'Sky', 'Blue', NULL, '2010', 'Sky', '£71,123,543');

INSERT INTO cyclist (cyclist_id, t_id, firstname, lastname, gender, Age)
VALUES
(NULL, NULL, 'Alex', 'Dowsett', 'Male', '28'),
(NULL, NULL, 'Nairo', 'Quintana', 'Male', '25'),
(NULL, NULL, 'Simon', 'Yates', 'Male', '22'),
(NULL, NULL, 'Adam', 'Yates', 'Male', '23'),
(NULL, NULL, 'Taylor', 'Phinney', 'Male', '24'),
(NULL, NULL, 'Stefan', 'Kung', 'Male', '21'),
(NULL, NULL, 'Chris', 'Froome', 'Male', '28'),
 (NULL, NULL, 'Geraint', 'Thomas', 'Male', '29');

INSERT INTO race(race_name, team_capacity, prize_money, main_sponsor)
VALUES 
('Tour De France', '12', '£2,500,000', 'Festina'),
('Tour of Britian', '6', '£250,000', 'Aviva'),
('Tour of Flanders' '8', '£1,250,000', 'TooGoodForOne');

---

DROP TABLE IF EXISTS bike, team, cyclist, race;

CREATE TABLE bike (
        id INT AUTO_INCREMENT,
        bike_brand VARCHAR (50),
        cost VARCHAR(50),
        colour VARCHAR (50),
        PRIMARY KEY (id)
);

CREATE TABLE team (
        id INT AUTO_INCREMENT,
        team_name VARCHAR (50),
        main_colour VARCHAR (50),
        b_id INT,
        year_established INT (50),
        sponsor VARCHAR (50),
        budget VARCHAR,
        PRIMARY KEY (id),
        FOREIGN KEY (b_id)
            REFERENCES bike (id)
        );

CREATE TABLE cyclist (
        cyclist_id INT AUTO_INCREMENT,
        t_id INT,
        firstname VARCHAR (50),
        lastname VARCHAR (50),
        gender CHAR (1),
        Age INT,
        PRIMARY KEY (cyclist_id),
        FOREIGN KEY (t_id)
                REFERENCES team (id)
);

CREATE TABLE race (
        race_name VARCHAR (50),
        team_capacity INT,
        prize_money VARCHAR(50),
        main_sponsor VARCHAR (50),
                PRIMARY KEY (race_name)
);

Answer 1

来自 Using Foreign Key Constraints 上的 Mysql 手册页:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

在你的例子中，你有 tinyint 和 int。那行不通，因为它们不一样。

最初的错误消息是，无论您在哪里运行它，它都会提示 t_id 不在 cyclist 表中。这可以通过运行命令 show create table cyclist

来验证