MySQL的抱怨外键约束内CREATE TABLE

问题描述：

我有以下~/mydb.sql文件：MySQL的抱怨外键约束内CREATE TABLE

CREATE DATABASE IF NOT EXISTS my_db CHARACTER SET utf8 COLLATE utf8_general_ci; 
SET default_storage_engine=INNODB; 

USE my_db; 

CREATE TABLE IF NOT EXISTS countries (
    country_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    country_name VARCHAR(250) NOT NULL, 
    country_label VARCHAR(250) NOT NULL, 
    country_description VARCHAR(500) NOT NULL, 
    country_code VARCHAR(25) NOT NULL, 

    CONSTRAINT pk_countries PRIMARY KEY (country_id), 
    INDEX idx_country_label (country_label), 
    INDEX idx_country_code (country_code), 
    CONSTRAINT uc_countries_name UNIQUE (country_name), 
    CONSTRAINT uc_countries_label UNIQUE (country_label), 
    CONSTRAINT uc_countries_desc UNIQUE (country_description), 
    CONSTRAINT uc_country_code UNIQUE (country_code) 
); 

CREATE TABLE IF NOT EXISTS states (
    state_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    state_name VARCHAR(250) NOT NULL, 
    state_label VARCHAR(250) NOT NULL, 
    state_description VARCHAR(500) NOT NULL, 
    state_abbrev VARCHAR(25) NOT NULL, 

    CONSTRAINT pk_states PRIMARY KEY (state_id), 
    INDEX idx_state_label (state_label), 
    INDEX idx_state_abbrev (state_abbrev), 
    CONSTRAINT uc_states_name UNIQUE (state_name), 
    CONSTRAINT uc_states_label UNIQUE (state_label), 
    CONSTRAINT uc_states_desc UNIQUE (state_description), 
    CONSTRAINT uc_state_abbrev UNIQUE (state_abbrev) 
); 

CREATE TABLE IF NOT EXISTS addresses (
    address_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    state_id BIGINT UNSIGNED NOT NULL, 
    country_id BIGINT UNSIGNED NOT NULL, 
    address_line_1 VARCHAR(250) NOT NULL, 
    address_line_2 VARCHAR(250), 
    address_line_3 VARCHAR(250), 
    address_city VARCHAR(250), 
    address_postal_code VARCHAR(25) NOT NULL, 

    CONSTRAINT pk_addresses PRIMARY KEY (address_id), 
    CONSTRAINT fk_addresses_states_state_id FOREIGN KEY state_id REFERENCES states (state_id), 
    CONSTRAINT fk_addresses_countries_country_id FOREIGN KEY country_id REFERENCES countries (country_id), 
    INDEX idx_addresses_line1_postal (address_line_1, address_postal_code), 
    CONSTRAINT uc_addresses_all UNIQUE (address_line_1, address_line_2, address_line_3, address_postal_code) 
);

当我登录到mysql命令行，并运行它，我得到一个外键定义的错误我addresses表：

mysql> source ~/mydb.sql 
Query OK, 2 rows affected (0.01 sec) 

mysql> source ~/tmp/testdb.sql 
Query OK, 1 row affected (0.00 sec) 

Query OK, 0 rows affected (0.00 sec) 

Database changed 
Query OK, 0 rows affected (0.02 sec) 

Query OK, 0 rows affected (0.02 sec) 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES states(state_id), 
    CONSTRAINT fk_addresses_countries_country_id FO' at line 12

我检查并重新检查了语法，并找不出我要去哪里的错误。任何人都可以发现它吗？

答

试试这个CREATE TABLE -

CREATE TABLE IF NOT EXISTS addresses (
    address_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    state_id BIGINT UNSIGNED NOT NULL, 
    country_id BIGINT UNSIGNED NOT NULL, 
    address_line_1 VARCHAR(250) NOT NULL, 
    address_line_2 VARCHAR(250), 
    address_line_3 VARCHAR(250), 
    address_city VARCHAR(250), 
    address_postal_code VARCHAR(25) NOT NULL, 

    CONSTRAINT pk_addresses PRIMARY KEY (address_id), 
    CONSTRAINT fk_addresses_states_state_id FOREIGN KEY (state_id) REFERENCES states (state_id), 
    CONSTRAINT fk_addresses_countries_country_id FOREIGN KEY (country_id) REFERENCES countries (country_id), 
    INDEX idx_addresses_line1_postal (address_line_1, address_postal_code), 
    CONSTRAINT uc_addresses_all UNIQUE (address_line_1, address_line_2, address_line_3, address_postal_code) 
);

问题的括号内。

MySQL的抱怨外键约束内CREATE TABLE

相关推荐