MySQL complaining about foreign key constraint inside CREATE TABLE

Question

I have the following ~/mydb.sql file:

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)
);

When I log into the mysql command line and run it I get an error on a foreign key definition for my addresses table:

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

I've check and rechecked the syntax and cannot figure out where I'm going awry. Can anybody spot it?

Answer 1

Try this 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)
);

Problem with brackets.