dss89001
2015-10-21 12:11
浏览 153
已采纳

关于外键的SQL语法错误

Could someone help me figure why I might be receiving the following syntax error when trying to run my SQL file please. It's related to my foreign key t_id which I created in the table cyclist. However everything is referenced etc and runs fine. Until I try to use it to input data...there must be something I'm missing

ERROR 1054 (42S22) at line 15: Unknown column 't_id' in 'field list'

Below is the SQL that's giving me the error (inputting data), below that I have copied the list of tables that I've created. Thank you.

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)
);
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongsisui7562 2015-10-21 12:28
    已采纳

    From the Mysql Manual page on Using Foreign Key Constraints:

    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.

    In your case you have tinyint and int. That won't work, as they are not the same.

    The initial error message is that wherever you are running this, it is complaining that t_id is not in the cyclist table. That can be verified with running the command show create table cyclist

    点赞 打赏 评论
  • doushi8231 2015-10-21 12:38

    Firstly, for auto increment columns you don't need to specify an insert value. Assuming a primary key constraint, this value will be auto inserted when the row is created.

    for example, reduce INSERT INTO team (id, team_name, main_colour, b_id, year_established, sponsor, budget) down to INSERT INTO team (team_name, main_colour, b_id, year_established, sponsor, budget)

    Primary keys CANNOT be null - they need to have a unique value. Your inserts are trying to assign null for primary key id's which will throw out additional errors.

    Secondly, t_id must not actually exist in the table itself for that error to be presented. Check to make sure any alterations have been committed to the database

    点赞 打赏 评论

相关推荐 更多相似问题